rmen220
asked on
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.Applic
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.Applic
set wb =xl.Workbooks.Open(strfile
set sht = wb.Worksheets(1) 'or wb.Worksheets("SheetName")
lngLastRow = sht.Range("A65536").End(xl
wb.Close SaveChanges:=False
set xl = nothing
DoCmd.TransferSpreadsheet acImport, , _
"Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.x
When I do I believe this line of code "set xl = CreateObject("Excel.Applic
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.Applic
set wb =xl.Workbooks.Open(strfile
set sht = wb.Worksheets(1) 'or wb.Worksheets("SheetName")
lngLastRow = sht.Range("A65536").End(xl
wb.Close SaveChanges:=False
set xl = nothing
DoCmd.TransferSpreadsheet acImport, , _
"Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.x
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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 FunctionASKER
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
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?
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?
ASKER
So here is what I did I placed this code into a module:
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\Bo
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(strfilen
Can you please step me through?
This sorry to be so frustrating can adjust the points on this question to 500
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 subYou 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\Bo
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(strfilen
Can you please step me through?
This sorry to be so frustrating can adjust the points on this question to 500
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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(xl
Run Time error 1004
application defined or object defined error
and this portion of the code is highlighted
lngLastRow = sht.Range("A65536").end(xl
Never occured to me that you might not have Excel as a reference in your application. Try:
lngLastRow = sht.Range("A65536").end(-4
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.
lngLastRow = sht.Range("A65536").end(-4
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.
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.
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.
ASKER
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
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.
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.
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.
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.
Open in new window