Open/Close xls files from Access (97)

Topic

New Reply

hi out there,

I have a macro that runs a query and then sends a lsit of names to a dumby excel file. Then on my main excel file i reference the list of names from the query to a column and use that as the source for my list box.

Here are the parts i need help on:
1) I know how to run the query and export the data to the dumby sheet

2) How can i make the next step of this macro to be to open up a desired excel file? Where would i enter it.? I am thinking there is a commmand line in module that i can use. This will be the main file that the dumby file gets refernced to.

3) I then need to close the dummy file as the next step of the macro. If there’s a command to open a file there should be on to close a file as well??

Does anyone out there know the syntax to open and close excel files from access?? Your help is greatly appreciated. Also, please be very detailedin your explanation because i am a VB newbie. Thank you again!!!

Ed

Ed

Reply | Quote

Replies

Ed,

Try this for starters:

Create a new module. Specify the Excel Object Library (find it on the Tools…References list and check it). Then type in the following code:

Option Explicit

Public Function blnExcelFromAccess() as boolean

Dim appExcel As Excel.Application

‘ Link to Excel using automation
Set appExcel = CreateObject(“Excel.Application”)

‘ So you can watch what’s happening…
appExcel.Visible = True

‘ Open your main workbook
appExcel.Workbooks.Open “C:My DocumentsExcel Workbooksmain file.xls”

‘ Calculate (‘read’ values from your dummy file; may not be necessary)
appExcel.Calculate

‘ Close the dummy workbook (without saving changes)
appExcel.Workbooks(“C:My DocumentsExcel Workbooksdummy file.xls”) False

‘ Done with Excel (omit if you want to leave Excel open)
appExcel.Quit

End Function

Save the module, then add a RunCode macro command to your macro routine and specify “=blnExcelFromAccess()” as the argument.

This is very bare bones, but it should get you started. I’ll bet you’ll be “doing it all” in VBA before long!

Tom

Reply | Quote

Oops… The close file code should read:

‘ Close the dummy workbook (without saving changes)
appExcel.Workbooks(“C:My DocumentsExcel Workbooksdummy file.xls”).Close False

Reply | Quote

Even with the fix to the close statement i still get an run time error 9′ – subscript out of range and it highlights that line

appExcel.Workbooks(“G:New IdeasDummy.xls”).Close False

ANy clue what this is or why it happnes? Thanks alot. If this can stop then it’ll be perfect, i hope.

Ed

Reply | Quote

I may be mistaken, but I think Access 97 (or is it Excel 97?) may need the 8+3 MS-DOS folder name rather than the civilized one; i.e., try changing “G:New IdeasDummy.xls” to “G:NEWIDE~1Dummy.xls”. (I’m using Office 2000 now, so I can’t check this myself.) It’s possible your MS-DOS folder name is different – check it by viewing the folder’s properties.

Tom

Reply | Quote

i dont think its the file name thats the problem because the program opens the dummy file with no problem. ive tried it the other way and i still get the same thing. maybe there is just a problem with the wording of the line:

appExcel.Workbooks(“G:NEWIDE~1Dummy.xls”).Close False

If the other commands look like appExcel.Workbooks.Open…, why doesnt this one have appExcel.Workbooks.Close… I’m just wondering????

Any more clues?? Thanks

Reply | Quote

My apologies. Two things wrong with my original suggestion. It now is apparent to me that you already have Excel running (with the dummy file open) when you run this macro. If so then you want to reference this instance of Excel, rather than starting up a new one. So, replace

Set appExcel = CreateObject(“Excel.Application”)

with

Set appExcel = GetObject(, “Excel. Application”)

(Note the comma after the opening parenthesis.)

Also, with that troublesome Close statement, forget the drive and path portion of the file name; just use

appExcel.Workbooks(“Dummy.xls”).Close False

(my mistake). Try the full name, too (e.g., “dummy file.xls”; my memory may be wrong about the MS-DOS filename thing).

I hope this gets you back on the path to happiness and good fortune.

Tom

Reply | Quote

Thanks Tom

A note for you, the first line with the appExcel didnt work when i changed it tp GetObject from Create Object. The error was that Active X could not GET. I just reverted back to the old one and it worked fine. The close statement is good now too. Here’s a little more food for thought, if after opening that main file in excel from access could i enter a line of code to run a macro ive made in excel? so it would be open dummy, open main, run xls macro, close dummy? Anything like that even possible?

And i must add that your da man!!!

Reply | Quote

Try something like:

appExcel.Run “main.xls!Module1.ExcelMacroToRun”

Fairly self-explanatory I suspect: ‘main.xls’ is the workbook where the macro resides (it must already be open in the ‘appExcel’ instance of Excel); ‘Module1’ is the module containing the macro of interest; and ‘ExcelMacroToRun’ is the macro name. If there are arguments to be passed to the macro, they should follow the quote string, separated by commas:

appExcel.Run “main.xls!Module1.ExcelMacroToRun”, arg1, arg2, arg3, …

Analogously, you can “drive” Access from Excel macros. Pretty cool stuff. If you want to explore further, I’d suggest you wade around the Object Model using the Object Browser in the Visual Basic Editor. It’s fairly remarkable what can be done with a little (sometimes a LOT) of poking around and experimentation.

Tom

Reply | Quote