Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Finding Next Row In Excel To Add Data

    Sorry, guy's i didn't change my lng to str



    please ignore

  2. #2
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    don't know how to delete post but sorted

    sorry for confusion

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think you can delete here but you can elsewhere - not that it's a good thing. All I'd do is comment like you did.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks Micron, i just done a silly billy thing, Set lngNo

    changed it to strNo to use string delimiters then didn't change the criteria in my recordset from lngNo to strNo

  5. #5
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    HI Micron and guy's, I thought i had this but I am having a problem with this, perhaps am I doing something wrong ?

    I am trying to add data to next available excel cell but coming up with remote server doesn't exist and sometimes trying to open a locked Excel file

    The line that is causing the problem is the (LR) last row, then i am trying to add data to next row, i can appear to get it working once but at 2nd attempt, it is coming up remote server not recognized ?

    The Item No in stock is always 6 digits but seems to work better with string for delimiters ?
    Code:
    Dim LR As Long, NR As LongDim strType As String
    Dim txtBox As String, strPath As String, strFile As String, sSQL As String, strItemNo As String
    Dim rs As DAO.Recordset
    Dim apXL As Object, xlWb As Object, xlWs As Object
    
    
    If Me.Stored = False Then
    Exit Sub
    End If
    
    
    If Me.Stored = True Then
    
    
    strItemNo = Me.ItemNo
    
    
        strFile = "T:\DMT Ltd\XL Files\Item Stock.xlsx"
    
    
    
        Set xl = CreateObject("Excel.Application")
        Set xlWrkBk = xl.Workbooks.Open(strFile)
        Set xlsht = xlWrkBk.Worksheets("Sheet1")
        xl.Visible = False
        LR = Cells(Rows.Count, 1).End(xlUp).Row (DEBUGGING)
       ' LR = Range("A:A").SpecialCells(xlCellTypeLastCell).Row
        NR = LR + 1
     
    
    
            'Set rs = CurrentDb.OpenRecordset("Select tblStock.StartQty, tblStock.ItemType, tblStock.ItemNo, tblStock.PONumber, tblStock.OrgQty, tblStock.SortNo " _
                    & "From tblStock " _
                    & "WHERE ItemType Is Not NULL Order By SortNo;") 'CREATED TO ADD ALL ITEMS
                    
            Set rs = CurrentDb.OpenRecordset("Select tblStock.StartQty, tblStock.ItemType, tblStock.ItemNo, tblStock.PONumber, tblStock.OrgQty, tblStock.SortNo " _
                    & "From tblStock " _
                    & "WHERE ItemNo = '" & strItemNo & "'") ' CREATED TO ADD A NEW ENTRY
                    
           ' xlsht.Cells(2, 1).CopyFromRecordset rs 'CREATED TO ADD ALL ITEMS
            xlsht.Cells(NR, 1).CopyFromRecordset rs ' TO ADD A NEW ENTRY
            xl.ActiveWorkbook.Save
            xl.ActiveWorkbook.Close
            xl.Quit
                
    End If
    
    
                Debug.Print rs.Fields
            
            Set xl = Nothing
            Set xlWrkBk = Nothing
           Set xlsht = Nothing

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I ask questions not as criticism but as potential changes while lacking certain knowledge of what someone is doing.
    If Me.Stored = false then it is automatically true? Or can it be null or zls? I suppose it doesn't matter about the latter 2 points since code is only supposed to execute if True.
    Do you have to use late binding?
    Why not just set strPath (or strFile) to the complete path?
    You are not using Option Explicit are you? As someone once posted, then you deserve what you get. Look at your app variable and Set statement. I stopped when I saw that.

    EDT - there are other similar mistakes, not to mention a bunch of unused variables.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Micron, thank you your honesty

    yes if the check box is false then exit sub otherwise execute

    i can change this after updating another field but kept to a check box more so for checking it working unchecking and rechecking just so i can see data going to the sheet

    once i get this, i want to change the event to another field that is updated and hide the check box field, I'd rather tick a box than play with critical data

    using option compare database when scrolling to the top of the vb screen

    the extra variables i can get rid of as it used to do different commands but now not used, i didn't think they would harm as they are sat dormant, have now removed them

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Just changed so the there is only 1 line for file strFile = the path and the file name.xlsx

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    using option compare database when scrolling to the top of the vb screen
    This is not good enough IMO. Add it below the compare statement and try compiling your code to see why. Then maybe try the following (but do add & compile otherwise you miss out on a learning opportunity). If I was a betting man, I'd bet you've been told this before your 816th post. This code is untested and not compiled (written in Notepad) and contains 1 or more traps to let me know if you tried to compile it using Option Explicit or not (unless you just find them anyway). Don't blow away your current procedure.
    Code:
    Dim strItemNo As String, sql As String
    Dim LRow As Long, NRow As Long
    Dim rs As DAO.Recordset
    Dim apXL As Object, xlWb As Object, xlWs As Object
    
    If Me.Stored = True Then
       strLiftNo = Me.LiftNo
       Set apXL = CreateObject("Excel.Application")
       xl.Visible = False
       Set xlWB = apXL.Workbooks.Open("T:\DMT Ltd\XL Files\Item Stock.xlsx")
       Set xlWs = xlWb.Worksheets("Sheet1")
       LRow = Cells(Rows.Count, 1).End(xlUp).Row '(DEBUGGING)
       'LRow = Range("A:A").SpecialCells(xlCellTypeLastCell).Row
        NRow = LR + 1
        'sql = CurrentDb.OpenRecordset("Select tblStock.StartQty, tblStock.ItemType," _
             & " tblStock.ItemNo, tblStock.PONumber, tblStock.OrgQty, tblStock.SortNo From" _
             & " tblStock WHERE ItemType Is Not NULL Order By SortNo;") 'CREATED TO ADD ALL ITEMS
    
        sql = "Select tblStock.StartQty, tblStock.ItemType, tblStock.ItemNo, tblStock.PONumber," _
               & " tblStock.OrgQty, tblStock.SortNo From tblStock WHERE" _
               & " ItemNo = '" & strItemNo & "'") ' CREATED TO ADD A NEW ENTRY
                    
        Set rs = CurrentDb.OpenRecordset(sql)
        'xlWs.Cells(2, 1).CopyFromRecordset rs 'CREATED TO ADD ALL ITEMS
         xlWs.Cells(NRow, 1).CopyFromRecordset rs ' TO ADD A NEW ENTRY
         With apXl
             .DisplayAlerts = False
             .xlWb.Save
             .xlWb.Close
             .DisplayAlerts = True 'maybe not req'd. Forget if app would reopen with alerts turned off
             .Quit
        End With
    End If
    
    Debug.Print rs.Fields
            
    Set apXl = Nothing
    Set xlWb = Nothing
    Set xlWs = Nothing
    EDIT - I moved Quit line; make sure you didn't copy the old version if you use that code.
    Last edited by Micron; 08-15-2022 at 12:28 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    using option compare database when scrolling to the top of the vb screen


    Option Explicit
    Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit

    You are defining variables and not even using them just making up new ones and they are not dimmed.

    Nothing seems to be learned.

    I doubt very much you would get access to an excel locked file, so you need to make sure it is not open.

    I know I have said this so many times, (especially with you) that it should be tattooed on my chest, but walk though your code and see what you actually have, not what you blithely think you have.

    How does the LR= line even work, (Debugging) is not commented out?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks Guy's for some reason it worked once then the 2nd time failed, so I have found changing this part totally fixed it

    Given an opportunity i am going took into setting Option Explicit and early / late binding because I haven't really concentrated on this before

    however, this problem is now solved by changing

    this

    Code:
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    To this

    Code:
    With xlWS
            lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
       End With
            nRow = lRow + 1
    thanks again for your help, always appreciated

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Go into the VBA window, then Tools/Options and make sure the Require Variable Declaration is ticked.

    That will add Option Explicit to all new code event etc. It will NOT add it to existing code, so you need to do that manually.
    This will highlight a lot of your error eraly on.

    When using Excel (or Word) commands from within Access, you have to qualify the object of that command.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks WGM, so it's good practice to that moving forwards regardless of new/old code ?

    ie: do it at next opportunity ?

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The With block is a bit of overkill? This does not tell your code what sheet your cell reference refers to:
    lRow = Cells

    Any valid sheet reference would suffice, such as ActiveSheet.Cells or Sheets("Sheet1").Cells or either of those with a qualified .Range reference.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks Micron, i can take back off the with block but it appeared to work every time after trying, i need to read more into binding as well and took on what WGM and checked the Require Variable Declaration

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-13-2019, 02:57 AM
  2. Finding Excel Row
    By DMT Dave in forum Access
    Replies: 6
    Last Post: 01-11-2019, 01:29 PM
  3. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  4. Finding and updating cells in an Excel worksheet
    By GraeagleBill in forum Programming
    Replies: 12
    Last Post: 01-10-2018, 06:55 PM
  5. Replies: 2
    Last Post: 02-10-2016, 09:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums