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

[JAGster]

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").

Function getData()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
 
    DoCmd.SetWarnings False
 
    path = "\\v:\scc-e\irg\Monthly SAP\"
 
    strFile = Dir(path & "*.xlsx")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Function
    End If
 

[ADezii]

The Code seems fine, I think the problem lies in the path Variable Assignment, namely:

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:

Dim strPath as String
strPath = "\\v:\scc-e\irg\Monthly SAP\"

[pdebaets]

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

path = "v:\scc-e\irg\Monthly SAP\"

[Mihail]

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.

[JAGster]

Thank you all for your help. The path was not valid as pedebaets pointed out.

[NeoPa]

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 ;-)

[JAGster]

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.