Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-26-2011, 02:09 AM   #1
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Question Run Time Error 2522

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;

Quote:
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

Eavie is offline   Reply With Quote
Old 09-26-2011, 02:19 AM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,421 Times in 2,387 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run Time Error 2522

Run the code again and do a Msgbox on these two variables to see what comes up:

lstExcelFiles.Value
pubRange(1)
vbaInet is offline   Reply With Quote
Old 09-26-2011, 02:31 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,441
Thanks: 51
Thanked 943 Times in 913 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Run Time Error 2522

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

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 09-26-2011, 04:17 AM   #4
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Re: Run Time Error 2522

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...
Eavie is offline   Reply With Quote
Old 09-26-2011, 04:22 AM   #5
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,421 Times in 2,387 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run Time Error 2522

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.
vbaInet is offline   Reply With Quote
Old 09-26-2011, 04:41 AM   #6
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Re: Run Time Error 2522

Ok all that comes up is a wee message box that says;

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

Does that mean anything?
Eavie is offline   Reply With Quote
Old 09-26-2011, 04:44 AM   #7
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,421 Times in 2,387 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run Time Error 2522

It means there's nothing in those variables and as a result your code won't work.

vbaInet is offline   Reply With Quote
Old 09-26-2011, 04:49 AM   #8
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Re: Run Time Error 2522

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?
Eavie is offline   Reply With Quote
Old 09-26-2011, 04:50 AM   #9
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,421 Times in 2,387 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run Time Error 2522

Can we see the code that saves the values into the variables?
vbaInet is offline   Reply With Quote
Old 09-26-2011, 04:55 AM   #10
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Re: Run Time Error 2522

Yeah, the code is saved in a module called globals and contains the following code;

Quote:
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
Eavie is offline   Reply With Quote
Old 09-26-2011, 04:58 AM   #11
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,421 Times in 2,387 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run Time Error 2522

Don't you have to run this function before trying to run your problem function?
vbaInet is offline   Reply With Quote
Old 09-26-2011, 05:00 AM   #12
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Re: Run Time Error 2522

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
Eavie is offline   Reply With Quote
Old 09-26-2011, 05:04 AM   #13
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,421 Times in 2,387 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Run Time Error 2522

Yes, I mean the updateConst() function should run before cmdRunImport_Click()

Was that line of code there initially?
vbaInet is offline   Reply With Quote
Old 09-26-2011, 05:06 AM   #14
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Re: Run Time Error 2522

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 is offline   Reply With Quote
Old 09-26-2011, 05:11 AM   #15
Eavie
Newly Registered User
 
Join Date: Aug 2011
Posts: 15
Thanks: 1
Thanked 0 Times in 0 Posts
Eavie is on a distinguished road
Re: Run Time Error 2522

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

Eavie is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run-time error 3075 Sintax error in string in querry expression triscaiulian Modules & VBA 3 09-22-2011 06:51 AM
Run Time Error 9 , trying to save Excel file, on 2nd time through loop heron_rider Modules & VBA 2 05-31-2011 10:20 AM
A question about the error message "Run-time error '3061' Too few parameters, Expecte dariyoosh Modules & VBA 7 03-12-2010 06:26 AM
keep getting this error Run time error 2501 This SendObject action was canceled. cursedeye Modules & VBA 5 10-14-2009 02:15 PM
Run time error 3075 - syntax error (missing operator) in query expression puskardas Modules & VBA 8 06-30-2008 07:35 PM




All times are GMT -8. The time now is 04:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World