469,270 Members | 1,002 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

How to fix code for Run-time Error 52, Bad file name or number?

26
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 in a different post that was written for Excel 2003 and Access 2003. I changed the path, the table name and the spreadsheet type but 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").

Expand|Select|Wrap|Line Numbers
  1. Function getData()
  2. Dim strFile As String 'Filename
  3. Dim strFileList() As String 'File Array
  4. Dim intFile As Integer 'File Number
  5. Dim filename As String
  6. Dim path As String
  7.  
  8.     DoCmd.SetWarnings False
  9.  
  10.     path = "\\v:\scc-e\irg\Monthly SAP\"
  11.  
  12.     strFile = Dir(path & "*.xlsx")
  13.     While strFile <> ""
  14.          'add files to the list
  15.         intFile = intFile + 1
  16.         ReDim Preserve strFileList(1 To intFile)
  17.         strFileList(intFile) = strFile
  18.         strFile = Dir()
  19.     Wend
  20.     If intFile = 0 Then
  21.         MsgBox "No files found"
  22.         Exit Function
  23.     End If
  24.  
Sep 20 '11 #1

✓ answered by pdebaets

I don't think that's a valid path. Try

Expand|Select|Wrap|Line Numbers
  1. path = "v:\scc-e\irg\Monthly SAP\"

6 38744
ADezii
8,800 Expert 8TB
The Code seems fine, I think the problem lies in the path Variable Assignment, namely:
Expand|Select|Wrap|Line Numbers
  1. path = "\\v:\scc-e\irg\Monthly SAP\"
P.S. - You may also wish to change the path String Variable to strPath so as to avoid confusion with the PATH used to set the Search Path for Executable Files, namely:
Expand|Select|Wrap|Line Numbers
  1. Dim strPath as String
  2. strPath = "\\v:\scc-e\irg\Monthly SAP\"
Sep 21 '11 #2
I don't think that's a valid path. Try

Expand|Select|Wrap|Line Numbers
  1. path = "v:\scc-e\irg\Monthly SAP\"
Sep 21 '11 #3
Mihail
759 512MB
Some more help:

When you are in DEBUG mode (your code stops because the error in line 12) press CTRL+G.
This will open the DEBUG window.
In this window type ?strPath and press ENTER. Now you can see the contain of strPath variable.
Is this value what you expect ?

I assume that you change the name for Path into strPath as ADezii teach you.
Sep 21 '11 #4
JAGster
26
Thank you all for your help. The path was not valid as pedebaets pointed out.
Sep 21 '11 #5
NeoPa
32,171 Expert Mod 16PB
I think you possibly missed that ADezii had already made that point in his post, but he has so many Best Answers already I doubt he'll miss an extra one ;-)
Sep 26 '11 #6
JAGster
26
Yes ADezil pointed out that the path was the issue, but pdebaets gave me the correct path. Specifically the need to remove the backslashes at the very start of the path.
Oct 6 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Brian Henry | last post: by
1 post views Thread by Daves | last post: by
5 posts views Thread by dbuchanan | last post: by
8 posts views Thread by Scott Emick | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.