Error message "Invalid outside procedure"

So I use the code below to import a range based on the last cell with data in column A.
 
When I do I believe this line of code "set xl = CreateObject("Excel.Application")" keeps creating the error message "invalid outside procedure"

I am pretty new at VBA. Do I just stick this code in a module in access and run it? Everytime I attempt to do so it asks me to create a macro. Could this be the problem?


Dim xl as object
Dim wb as object
Dim sht as object
Dim lngLastRow as long

set xl = CreateObject("Excel.Application")
set wb =xl.Workbooks.Open(strfilename)
set sht = wb.Worksheets(1)  'or wb.Worksheets("SheetName")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
set xl = nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.xlsx", True, Range("A1:U" & lngLastRow)
rmen220Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupNerdCommented:
Its not clear if you have done this, but place the code in a module, making sure the code is wrapped in a function or sub declaration like this:


Sub ExcelProcedure()

Dim xl as object
Dim wb as object
Dim sht as object
Dim lngLastRow as long

set xl = CreateObject("Excel.Application")
set wb =xl.Workbooks.Open(strfilename)
set sht = wb.Worksheets(1)  'or wb.Worksheets("SheetName")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
set xl = nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.xlsx", True, Range("A1:U" & lngLastRow)

End Sub

Open in new window


Save your module with a name that is different from the sub, like 'modExcelCode'
mbizupNerdCommented:
Also, I believe you'll have to modify your transfer spreadsheet command like this:


DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.xlsx", True, "A1:U" & lngLastRow

Open in new window

Dale FyeOwner, Dev-Soln LLCCommented:
I doubt that CreateObject( ) is the problem, as I have never really had a problem with that.  

If you put this code in a subroutine or function, as I would expect, did you pass it the variable for strFileName?  You could pass that via the declaration statement, or use the File Open/Save API to select the file.

I actually use a function to get an Excel object.  You would use it like:

set xl = GetExcel

and the function is below.  This function looks to see whether there is already an instance of Excel open, and if so, uses it.  If not, it opens a new one.  I have a lot of code that exports or reads data from Excel, so I found that creating this funtion allow me to avoid a lot of code in my various modules.
Public Function GetExcel() As Object    'Excel.Application

    On Error GoTo ProcError
    
    Set GetExcel = GetObject(, "Excel.Application")
    
    Exit Function

ProcError:
    If Err.Number = 429 Then
        Set GetExcel = CreateObject("Excel.Application")
    Else
        Set GetExcel = Nothing
    End If

End Function

Open in new window

5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

rmen220Author Commented:
Sorry to be such a novice. Maybe I should explain what I am trying to do. I would like to create a procedure (not sure "procedure" is the right term) that I can call through a macro that will import the data range based on the code above. When the Sub ExcelProcedure() above I now get- Compile error: Sub or function not defined and the range portion of this part of the code is highlighted in blue Range("A1:U" & lngLastRow. Thank you for all your help. Thank goodness for EE
Dale FyeOwner, Dev-Soln LLCCommented:
I didn't even look at that line when I provided my subroutine in your previous post, because you implied that it was working when you used that technique.  See mbizup's post above.

OK, calling this from a macro is new, why a macro when you could use code.  Although the Access wizards like to write code that uses macros, you can do so much more with VBA, and we are here to help!

I assume you want to call this when you press a button on your form, is that correct?  If so, are you displaying the name of the file you want to import from anywhere on the form?  Where are you getting that value from.  Can you take a screen shot (PrintScreen) of your form and post it here so we can see what it looks like?
rmen220Author Commented:
So here is what I did I placed this code into a module:


Sub ExcelProcedure()

Dim xl As Object
Dim wb As Object
Dim sht As Object
Dim lngLastRow As Long

Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(strfilename)
Set sht = wb.Worksheets(1)  'or wb.Worksheets("SheetName")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
Set xl = Nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "C:\Documents and Settings\u0k0ao\Desktop\Book123.xls", True, "A1:U" & lngLastRow

end sub

Open in new window


You will notice i made the adjustment from mbizup's reply and removed range from
 ""A1:U" & lngLastRow" I now get the error

"runtime error 1004: "could not be found check the spelling of the filename and verify the location is correct"

The path in this portion of the code is def correct
DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "C:\Documents and Settings\u0k0ao\Desktop\Book123.xls", True, "A1:U" & lngLastRow

I suspect this may stem from fyed reply regarding this part of code and needing to make an adjustment there

Set wb = xl.Workbooks.Open(strfilename)

Can you please step me through?
This sorry to be so frustrating can adjust the points on this question to 500
Dale FyeOwner, Dev-Soln LLCCommented:
Try this:

Sub ExcelProcedure()

Dim xl As Object
Dim wb As Object
Dim sht As Object
Dim lngLastRow As Long
Dim strFileName as string

Set xl = CreateObject("Excel.Application")

strFileName = "C:\Documents and Settings\u0k0ao\Desktop\Book123.xls"
Set wb = xl.Workbooks.Open(strfilename)

'If the sheet you are importing is the 1st worksheet in the workbook, you should
'be able to use either of these options.
Set sht = wb.Worksheets(1)  'or wb.Worksheets("Jan2013_RD_NonEnd")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
Set xl = Nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", strFileName, True, "A1:U" & lngLastRow

end sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rmen220Author Commented:
Maybe this will help I have data that is loaded into excel workbooks that house results for my agents I use excel to format that data then import into access where I link the tables to create one DB that combinds all the metrics from those excel WB's.
Some workbooks are updated daily, some weekly, some monthly. So I have to create a procedure be it macro or module that will allow me to designate when I want the data to import by running the macro or module.    

You are right "macro" was the wrong term I just want to run the procedure when I designate ex. click on the module
rmen220Author Commented:
so I pasted the code above into my module and recieved the error message

Run Time error 1004

application defined or object defined error

and this portion of the code is highlighted

lngLastRow = sht.Range("A65536").end(xlup).row
Dale FyeOwner, Dev-Soln LLCCommented:
Never occured to me that you might not have Excel as a reference in your application.  Try:

lngLastRow = sht.Range("A65536").end(-4162).row

If you open the VBA window, then select Tools -> References, you can scroll down and check the box next to Microsoft Excel, this will give you the ability to use the Excel constants like xlup.
Dale FyeOwner, Dev-Soln LLCCommented:
As much as I appreciate all of these points (2 separate posts), I don't deserve them, since I didn't really get the first solution right to begin with.

As a minimum, you need to give some of these points to mbizup, as she recognized that the reference to the range in the TransferSpreadsheet call was wrong.

Just click the "Request Attention" button (bottom right of your original post) and ask the moderators to reopen the question for reallocation of points.
rmen220Author Commented:
You are correct (sorry mbizup) I got overly excited having the solution. I am new to EE too. Dang I sure did learn alot with this one post
Dale FyeOwner, Dev-Soln LLCCommented:
Glad we could help.

Just some advise, although you may not know the right syntax, make sure you search the articles here before asking a question, unless you just have to have the answer right now.

There are very few Access questions that have not already been asked and answered here.

Also, Take a look at some of the Access Webinars.
mbizupNerdCommented:
rmen220,

Welcome aboard!

No worries - I was in a meeting.  Glad you're sorted.

One of the best things you can do to get the most out of your membership here is to go through the pages on asking questions in EE's help.  It contains a lot of valuable info with screenshots and screen casts to boot.



Dale,

Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.