Opening Excel From Access

[jimmy.jowers08]

I am a beginer level Access user. i have basic knowledge of how to build and manipulate an Access database. I am trying to write code to open multiple excel workbooks one at a time from Access. I succesfully wrote the code using a run command macro that opens one excel file. See Below:

Function OpenExcelFromAccess()
Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")


With MyXL
.Application.Visible = True
.Workbooks.Open "C:\Users\jameshe\Documents\.00estimate tools DB\MY GOODY BOX\Conduit Fill AND VOLTAGE DROPCalculator.xls"
End With
End Function

My problemn is that i need to open several other workbooks but not all at the same time. When i try to add a macro/module to do the same thing as the one that works (with a different file path) I get an error code that says "The expression contains an ambiguous name." I tried adding this into the exisitng module and it will open both at the same time. Can someone help me understand what i am doing wrong.
Set MyXL = CreateObject("Excel.Application")
With MyXL
.Application.Visible = True
.Workbooks.Open "C:\Users\jameshe\Documents\.00estimate tools DB\MY GOODY BOX\Arc Flash Calculator.xls"
End With

[ItsMe]

I suggest you make a reference to the Microsoft Excel XX.X Object Library. You can do this from the VBA Editor. Go to Tools>References and then select the library by ticking the check box and clicking OK. With that you can use a different way to declare your Object Variables. I pasted some code below that uses this technique. The code opens a file in the path specified and renames Sheet1 to Bob. Check it out and see if that example helps at all.

By using the reference to the library, you get the advantage of Intellisense as you type your code.

    Dim xlx As New Excel.Application
    Dim xlw As Workbook
    Dim xls As Worksheet
    'Dim MyRange As Range
    Set xlw = xlx.Workbooks.Open("C:\Test\ExcelFiles\TestFile.xlsx")
    Set xls = xlw.Worksheets("Sheet1")
'    Set xls = xlw.Worksheets(1)     'This option here is going after the index vs. the "name"
    
    xls.Name = "Bob"
    
    xlw.Save
    
    Set xls = Nothing
    xlw.Close
    Set xlw = Nothing
    Set xlx = Nothing

[ssanfu]

In addition to ItsMe's comments, you might check out Ken Snell's site:

Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

[jimmy.jowers08]

I must be missing something. I copied the code you supplied changing the file path and it keeps asking for a Macro name which it enters as a sub.

[June7]

Where did you put the function? Could be a Sub instead of Function.

If all you want to do is open the Excel file and not manipulate it, consider FollowHyperlink or review http://allenbrowne.com/func-GoHyperlink.html or use Shell:

Dim wsShell As Object
Set wsShell = CreateObject("WScript.Shell")
wsShell.Run Chr(34) & "C:\Test\ExcelFiles\TestFile.xlsx" & Chr(34)

[jimmy.jowers08]

OK, what i am trying to open are a bunch of different calculation tools. For example, one workbook is a calculator to figure out conduit fill. I need to be able to manipulate certain fields in the excel to return values so i can't just open them as a read only. In the long run i will convert the excel workbooks into queries and forms in Access. In the interim i need to be able to open the files in Access. This seems like simple task but my lack of VBA knowledge is making it a chore.

the code i am currently using was based on a google search so i dont understand what i did, i just know it works for one workbook only.

[June7]

Manipulate how? Do you need to set values in cells?

[jimmy.jowers08]

Basicallly i need to input values in the excel which performs the math inside of the workbook. i do not need to send the values back to Access. I just need to be able to open different workbooks from the same folder but only as i need them. Right now there are 4 workbooks that i need to include. Each one is a different tool. This is just a quick fix until i can actually re-create them in the Access program.

[June7]

So you have 4 buttons with code in each Click event?

Since you don't need to send data back to Access, probably want to set the Excel app to visible after programmatically setting values in cells.

xlx.Visible = True

instead of Close and Quit.

[jimmy.jowers08]

I will have 4 buttons on the Switchboard. Right now only one works. The code i originally posted works to open one. if i add the second set of code i posted to the first one i can open all 4 at the same time. i only want to open one in each instance?

[ItsMe]

I must be missing something. I copied the code you supplied changing the file path and it keeps asking for a Macro name which it enters as a sub.

Can you post the code you used here? I suggest creating a new form and a new button. Start with a clean form and button.

[jimmy.jowers08]

Function OpenExcelFromAccess()
Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")
With MyXL
.Application.Visible = True
.Workbooks.Open "C:\Users\jameshe\Documents\.00estimate tools DB\MY GOODY BOX\Conduit Fill AND VOLTAGE DROPCalculator.xls"
End With
End Function

[jimmy.jowers08]

Function OpenExcelFromAccess()
Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")
With MyXL
.Application.Visible = True
.Workbooks.Open "C:\Users\jameshe\Documents\.00estimate tools DB\MY GOODY BOX\Conduit Fill AND VOLTAGE DROPCalculator.xls"
End With
Set MyXL = CreateObject("Excel.Application")
With MyXL
.Application.Visible = True
.Workbooks.Open "C:\Users\jameshe\Documents\.00estimate tools DB\MY GOODY BOX\Arc Flash Calculator.xls"
End With
End Function

This one opens both workbooks at the same time.

[ItsMe]

Here is an example of two buttons.

[jimmy.jowers08]

im still tryig to figure out how to attach.