Run Time Error 2522 (1 Viewer)

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
Hi all,
I have created a database that imports mulitple spreadsheets as needed.
An Import button on my form brings up another form that lists the Excel files that I can import.
It works for my past databases but when I click Import and it brings up my second form it says 'Run Time Error 2522- The action or method requires a File Name argument'
It is debugging to the line highlighted in purple;

Private Sub cmdRunImport_Click()
On Error GoTo errHandler
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "temporary_data", pubPath(1) & lstExcelFiles.Value, False, pubRange(1)
appImport
DoCmd.OpenForm "frmSubmitKH08", acNormal
DoCmd.Close acForm, "frmImportKH08"
errHandler:
If Err.Number > 0 Then
If Err.Number = 3011 Then
MsgBox "The Spreadsheet " & lstExcelFiles.Value & " is not in the format required for import"
ElseIf Err.Number = 3274 Then
MsgBox "The Workbook " & lstExcelFiles.Value & " is protected. Unprotect the Workbook for Import."
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
End If
End Sub

Basically I need to know if this can be fixed?
I've looked up multiple sites about this error and they mention things about your register etc. but I don't have access on this computer to check my registry. Can this be fixed in any simpler way?

Many Thanks,
Eavie
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Run the code again and do a Msgbox on these two variables to see what comes up:

lstExcelFiles.Value
pubRange(1)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:25
Joined
Sep 12, 2006
Messages
15,613
if you have used it before, it should be OK, but the pubrange variable needs a leading exclamation point, I think, top address aparticular workbook tab.

I would definitely check that is formatted correctly, as noted by VBAInet
 

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
How do I run them with a message box, still reasonably new to coding databases so I'm still learning.
That exact code works for a previous database but is refusing to work for a new one, both are saved in the exact same place on the same drive...
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Replace the full code with this:
Code:
MsgBox "pubPath(1): " & pubPath(1) & vbNewLine & "lstExcelFiles.Value: " & lstExcelFiles.Value & vbNewLine & "pubRange(1): " & pubRange(1)
'On Error GoTo errHandler
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "temporary_data", pubPath(1) & lstExcelFiles.Value, False, pubRange(1)
'appImport
'DoCmd.OpenForm "frmSubmitKH08", acNormal
'DoCmd.Close acForm, "frmImportKH08"
'errHandler:
'If Err.Number > 0 Then
'If Err.Number = 3011 Then
'MsgBox "The Spreadsheet " & lstExcelFiles.Value & " is not in the format required for import"
'ElseIf Err.Number = 3274 Then
'MsgBox "The Workbook " & lstExcelFiles.Value & " is protected. Unprotect the Workbook for Import."
'Else
'MsgBox Err.Number & vbCrLf & Err.Description
'End If
'End If
Click the button and show us a screenshot or tell us what it reads.
 

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
Ok all that comes up is a wee message box that says;

pubPath(1):
lstExcelFiles.Value:
pubRange(1):

Does that mean anything?
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
It means there's nothing in those variables and as a result your code won't work.
 

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
The pubPath and pubRange variables are pulled from a path and range table... I don't understand why they won't work on this database if they work for others.
Do you think it is something to do with my registry, meaning it can't find the location of the excel files and therefore can't use a range?
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Can we see the code that saves the values into the variables?
 

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
Yeah, the code is saved in a module called globals and contains the following code;

Public pubQUARTER As Date
Public pubDEFAULTQUARTER As Date
Public pubRange(12) As String
Public pubPath(4) As String
Public pubFormName As String

Public Sub updateConst()
Dim db As Database
Dim i As Integer
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM range")
Do Until rs.EOF
i = i + 1
pubRange(i) = rs.Fields("rangeSheetName") & "!" & rs.Fields("rangeStart") & ":" & rs.Fields("rangeEnd")
rs.MoveNext
Loop
Set rs = db.OpenRecordset("SELECT * FROM path")
i = 0
Do Until rs.EOF
i = i + 1
pubPath(i) = rs.Fields("pathLink")
rs.MoveNext
Loop
db.Close
Set db = Nothing
pubDEFAULTQUARTER = DLookup("quarter", "quarters", "defaultQuarter='DEFAULT'")
End Sub
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Don't you have to run this function before trying to run your problem function?
 

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
Do you mean run the updateConst() function?
I tried that before and in my previous databases it wouldn't work so I took that line of code out and it worked fine...
I probably sound like a complete novice but I really appreciate your help
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Yes, I mean the updateConst() function should run before cmdRunImport_Click()

Was that line of code there initially?
 

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
Ah I get what you mean, no there was no other code to begin with but I'll try and run the updateConst() function before the Import function now and see what happens
 

Eavie

Registered User.
Local time
Today, 15:25
Joined
Aug 16, 2011
Messages
15
OK I put the update function in and it made no difference, the same error message still came up and it still didn't list my Excel forms
 

Users who are viewing this thread

Top Bottom