Inside Access 2007 open Excel

[dinfinity]

I am looking for the VBA code that will let me open an excel workbook and run a macro in it then close the workbook.
I am transfering in data from excel, but it needs manipulation first.
Using Access 2007 and Excel 2007.

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook
Dim f As Object
Dim myfile As String
Set xlsApp = CreateObject("Excel.Application")
Set f = Nothing
myfile = ""
 
Set f = Application.FileDialog(3)
f.Title = "SELECT PBUS DATA FILE TO IMPORT"
f.AllowMultiSelect = False
f.Show
With f
   myfile = .SelectedItems(1)
End With
 
Set xlswkb = GetObject([myfile])

This basic code right here continues to get hung up on the GetObject

Thx

 

[DJkarl]

GetObject is used to retrieve already opened objects.

Since you've already created an Excel.Application object use that to open the file.

set xslwkb = xslapp.Workbooks.Open(PathToFileHere)

 

[dinfinity]

Now I get an error message that says object needed

 

[DJkarl]

What line is it saying that on?

 

[boblarson]

Probably the typo:

set xslwkb = xslapp.Workbooks.Open(PathToFileHere)

should be:

set xslwkb = xlsapp.Workbooks.Open(PathToFileHere)

 

[boblarson]

In the future, please use code tags when posting code, especially if it is more than just a couple of lines.

 

[dinfinity]

Ok now I get an error - Automation error The server threw an exception
The error happens at this line:

Set xlswkb = xlsApp.Workbooks.Open([myfile])

 

[boblarson]

Take myfile out of the square brackets.

 

[dinfinity]

Same error with or without brackets.

 

[boblarson]

Okay, time to have you post what code you currently have now that you've been fixing it.

 

[dinfinity]

Here it is. I am wondering if their is some sort of security setup on this computer that might be causing the problem. After running the code and it errors, I try to open the excel file manually and excel reports a problem then needs to restart excel. After the excel shut-down and restart everything works fine manually. I have tried to open .xls files and .xlsx files same result.
Running windows vista no admin rights.
Here is the code

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook
Dim f As Object
Dim myfile As String
Set xlsApp = CreateObject("Excel.Application")
Set f = Nothing
myfile = ""

Set f = Application.FileDialog(3)
f.Title = "SELECT PBUS DATA FILE TO IMPORT"
f.AllowMultiSelect = False
f.Show
With f
myfile = .SelectedItems(1)
End With
Set xlswkb = xlsApp.Workbooks.Open(myfile)
Exit_Command8_Click:
Exit Sub
Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click
End Sub

 

[DJkarl]

If this has been crashing several times you may have hidden Excel processes open which can cause automation (and other) issues. Have you checked the process list in the task manager, see if there are any Excel.exe processes, if so and you don't have Excel open get rid of them and try again.

 

[dinfinity]

Nothing in task manager. Same automation error. Did notice after that an excel application stays open in task manger even though nothing is open. I did a end process and tried again same automation error.

 

[boblarson]

The code should work as you currently have it. But if you have any other code that is doing stuff with Excel, then you could end up with a situation like DJkarl has pointed out. Read this as a precaution:

Also, you can get rid of these two lines of code. They are not necessary:

Set f = Nothing
myfile = ""

When you have a procedure where DIM is used. Each time the procedure runs it is going to create those variables and so if it existed it would be destroyed at that time too. So, you don't need to set f = nothing since it will not exist until it is instantiated. And for myfile, it already is a vbNullString which means you don't have to clear it because until you give it something, it already is that.

 

[boblarson]

Are you making sure that you have selected a proper Excel file with the dialog?

 

[DJkarl]

Assuming that your myfile exists

try changing

Set xlsApp = CreateObject("Excel.Application")

to

Set xlsApp = new Excel.Application

Shouldn't really matter, just curious if makes a difference.

 

[boblarson]

And if there is a phantom one there then you need to read this (I forgot to include it in my other post )

http://www.btabdevelopment.com/ts/excelinstance

 

[dinfinity]

Yes the excel files are correct and I have been checking for other processes running. I guess all I can do is see if the program will work on a computer I have full rights too and know what security is in place.