Results 1 to 14 of 14
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    For Loop error #3314- while reading Excel file using VBA

    Some of my Excel files have only 2 rows , some files have 160 rows/records how can I change the FOR loop accordingly?


    or is there a better way to check and update the table?

    Code:
    Set xlc = xls.Range("A3")
    
    Set rst = CurrentDb.OpenRecordset("B_temp") ', dbOpenDynaset, dbAppendOnly)
    
    For i = 0 To 200
              rst.AddNew
                For lngColumn = 0 To rst.Fields.Count - 1
                     rst.Fields(lngColumn).Value = xlc.Offset(i, lngColumn).Value
                    ' MsgBox xlc.Offset(i, lngColumn).Value
                     Next lngColumn
                     rst.Fields("File_name") = strFileName
            rst.Update
        Next i
    End If
    Thank you in advance

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the error description? I would test the Excel value and exit the loop when it's blank.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is what I use to determine the last row in an Excel "list" (continuous range):
    Code:
    lastRow = oWS.Range("A" & oWS.Rows.Count).End(xlUp).Row
    lastrow is a long type variable, oWS is an Excel.Spreadsheet

    Cheers,
    Vlad

  4. #4
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Run-time error ‘3314’:
    You must enter a value in the “FirstName” field

    When I was reading the excel file I only have 2 records. After the loop goes to A5 first field, which is blank. I get the above message when the cursor reads this line: rst.Update

    Code:
    Set xlc = xls.Range("A3")Set rst = CurrentDb.OpenRecordset("B_temp") ', dbOpenDynaset, dbAppendOnly)
    
    If xlc.Value = " " Then
    Exit Sub
    Else

    [/CODE][/CODE]

  5. #5
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    I changed my code..

    Dim lastrow As Long

    lastrow = xls.Range("A" & xls.Rows.Count).End(xlUp).Row

    Getting the following error.

    Run-time error ‘1004’
    Application-defined or object-defined error

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could you post your entire sub please?

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here are some links regarding how to find LastRow in Excel VBA:

    https://www.thespreadsheetguru.com/b...lumn-using-vba
    https://stackoverflow.com/questions/...t-row-in-range

    Cheers,
    Vlad

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I do this kind of thing, or test the value as described above:


    Code:
    LastRow = xl.ActiveSheet.UsedRange.Rows.Count
    R = 2 'skip the header row
    
    For X = 1 To 10000
      'bunch of code here
      R = R + 1
      If R > LastRow Then Exit For
    Next X
    LastRow and R are declared as Long.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Code:
    Private Sub cmd_imp_Click()
    On Error GoTo Err_cmd_imp_Click
    Dim lngColumn As Integer 
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    'Dim lastrow As Long
    Dim rst As DAO.Recordset, MyRec As DAO.Recordset, Myext As DAO.Recordset
    Dim blnEXCEL As Boolean
    blnEXCEL = True
      Dim strFileName As String
      Dim strFileLoc As String
      Dim Filepath As String
      Dim StrSheetName As String
       
      Dim f As Object
       Dim varItem As Variant
     
        Set f = Application.FileDialog(3)
        f.AllowMultiSelect = True
        If f.Show Then
            For Each varItem In f.SelectedItems
                strFileName = Dir(varItem)
                strFileLoc = Left(varItem, Len(varItem) - Len(strFileName))
                strFilePath = strFileLoc & strFileName
             Next
        End If
          Dim temparray() As String
          temparray = Split(strFileName, "_") ' this is to capture the date from the filename 
      ' Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.Application")
          blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0
     
    xlx.Visible = True
      DoCmd.SetWarnings False
      Set xlw = xlx.Workbooks.Open(strFilePath, , True)
      Set xls = xlw.Worksheets(2)  
       DoCmd.SetWarnings False
       DoCmd.RunSQL "Delete * from B_temp;"
       DoCmd.SetWarnings True
    ' first data value (non-header information) that contains data
    Set xlc = xls.Range("A3")
    Set rst = CurrentDb.OpenRecordset("B_temp") ', dbOpenDynaset, dbAppendOnly)
    ' write data to the recordset
    If xlc.Value = " " Then
    Exit Sub
    Else
    For i = 0 To 200
              rst.AddNew
                For lngColumn = 0 To rst.Fields.Count - 1
                     rst.Fields(lngColumn).Value = xlc.Offset(i, lngColumn).Value
                    Next lngColumn
                     rst.Fields("File_name") = strFileName
                     rst.Fields("Date_FileReceived") = Format(temparray(1), "##/##/####")
        rst.Update
        Next i
    End If
    rst.Close
    Set xls1 = Nothing
    xlw.Close False
    Set xlw = Nothing
    If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing
    Exit_cmd_imp_Click:
        Exit Sub
    Err_cmd_imp_Click:
         MsgBox "Error No: " & Err.Number _
           & vbNewLine _
           & Err.Description, _
           vbExclamation + vbOKOnly, _
           "Error Information"
      Resume Exit_cmd_imp_Click
    End Sub

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Have you tried any of the suggested methods?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Yes, I tried each and every method suggested above.
    for this code below I got runtime error 424:
    Code:
    lastrow = xlw.Worksheets(2).UsedRange.Rows.Count
    R = 2 'skip the header row
     R = R + 1
      If R > lastrow Then Exit For
        Next i
    Run-time error'424':
    Object required

    I realized that my excel template has 100 fixed rows, so my "lastrow" is shows 100. But there are only 2 records entered. because of that I am getting error message.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Then I'd test the value in the spreadsheet before adding and jump out of the loop when it's blank.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    How can I test the value in spreadsheet?

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This type of thing should work:

    If Len(xl.cells(R, 8)) > 0 Then

    Where xl is your Excel instance, R is the row number counter, and 8 is the column to be tested.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reading .xls file using VBA code
    By stalk in forum Programming
    Replies: 3
    Last Post: 01-19-2017, 01:58 PM
  2. Replies: 5
    Last Post: 05-15-2015, 10:49 AM
  3. Reading Excel file from Share point
    By selvakumar.arc in forum Import/Export Data
    Replies: 1
    Last Post: 03-27-2014, 02:00 PM
  4. Replies: 4
    Last Post: 02-05-2014, 12:17 PM
  5. Replies: 3
    Last Post: 05-30-2012, 01:43 PM

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