Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open/Close xls files from Access (97)

    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

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    Oops... The close file code should read:

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

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    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

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    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

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    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 <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  8. #8
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    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!!!

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open/Close xls files from Access (97)

    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 <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •