1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Debugging for Access 2007 VBA Run-time Error 52 - Bad file name or number.

Discussion in 'Business Applications' started by Teachjag, Sep 20, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Teachjag

    Teachjag Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    9
    I don’t know VBA but have been using Access for awhile using tables, queries, forms and macros. I am trying to automate importing a number of Excel 2007 spreadsheets into an Access 2007 table. The import will be done each month, but the number of spreadhseets could be different. The spreadsheets will all be saved to the same folder. I need to import all the spreadsheets in the folder into the same table. I found VBA code that was written for Excel 2003 and Access 2003. I changed the path, the table name and the spreadsheet type. When I run the code I get Run-time error 52, Bad file name or number.

    When I debug the code, it highlights the line this line --strFile = Dir(path & "*.xlsx").

    Any help will be greatly appreciated!

    Code:
    [COLOR=black][FONT=Arial][COLOR=black][FONT=Arial][FONT=Calibri][SIZE=3]Function getData()[/SIZE][/FONT][/FONT][/COLOR][FONT=Arial]
    [COLOR=black][FONT=Calibri][SIZE=3]Dim strFile As String 'Filename[/SIZE][/FONT][/COLOR]
    [COLOR=black][FONT=Calibri][SIZE=3]Dim strFileList() As String 'File Array[/SIZE][/FONT][/COLOR]
    [COLOR=black][FONT=Calibri][SIZE=3]Dim intFile As Integer 'File Number[/SIZE][/FONT][/COLOR]
    [COLOR=black][FONT=Calibri][SIZE=3]Dim filename As String[/SIZE][/FONT][/COLOR]
    [COLOR=black][FONT=Calibri][SIZE=3]Dim path As String[/SIZE][/FONT][/COLOR]
     
    [COLOR=black][SIZE=3][FONT=Calibri]   DoCmd.SetWarnings False[/FONT][/SIZE][/COLOR]
     
    [COLOR=black][SIZE=3][FONT=Calibri]   path = "\\c:\Monthly Data\"[/FONT][/SIZE][/COLOR]
     
    [COLOR=black][SIZE=3][FONT=Calibri]   strFile = Dir(path & "*.xlsx")[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]   While strFile <> ""[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]        'add files to the list[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]       intFile = intFile + 1[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]       ReDim Preserve strFileList(1 To intFile)[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]       strFileList(intFile) = strFile[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]       strFile = Dir()[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]   Wend[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]   If intFile = 0 Then[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]       MsgBox "No files found"[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]       Exit Function[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]   End If[/FONT][/SIZE][/COLOR]
     
    [COLOR=black][SIZE=3][FONT=Calibri]   'cycle through the list of files[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]   For intFile = 1 To UBound(strFileList)[/FONT][/SIZE][/COLOR]
     
    [COLOR=black][SIZE=3][FONT=Calibri]       filename = path & strFileList(intFile)[/FONT][/SIZE][/COLOR]
    [COLOR=black][SIZE=3][FONT=Calibri]       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2XML, "tblPartInfoMonthlyData", filename, True[/FONT][/SIZE][/COLOR]
     
    [COLOR=black][SIZE=3][FONT=Calibri]   Next intFile[/FONT][/SIZE][/COLOR]
     
    [COLOR=black][SIZE=3][FONT=Calibri]  DoCmd.SetWarnings True[/FONT][/SIZE][/COLOR]
     
    [COLOR=black][FONT=Calibri]End Function[/FONT][/COLOR]
    [/FONT][/FONT][/COLOR]
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    And in debug what does the path variable say when you hover the mouse over it? I am not sure you need the extra \\ in the path.
     
  3. Teachjag

    Teachjag Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    9
  4. Teachjag

    Teachjag Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    9
    More precisely,
    When I hover the mouse over strFile it shows this:
    strfile=""

    When I hover the over the word path it shows this:
    path=\\c:\Monthly Data\
     
  5. Teachjag

    Teachjag Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    9
    Thanks Rockn

    As you suggested, I removed the \\ at the start of the path. Which fixed the Run-time Error 52.

    Then I fixed a couple of typos in the spreadsheet type, I had acSpreadsheetTypeExcel2XML and it should have been acSpreadsheetTypeExcel12Xml.

    I'm not getting any errors now but none of the info in the spreadsheets is importing to the table. Any suggestions?
     
  6. Teachjag

    Teachjag Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    9
    I found the other issues and its working!

    Thanks for the tip Rockn about the extra \\ in the path!
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1018616

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice