RunApp command in Access is breaking my Excel macro

[scun]

Hi, first off, I'm a bonafide idiot that has no place making any kind of database from the ground up, but here I am. My apologies in advance.

I've been stuck on this problem for a couple of days now and hope you guys can help.

I'm trying to do the following:
1) export data from the results of an Access query to an Excel workbook (princfull-pl.xls)
2) open that workbook.
3) Open a second excel workbook (principles-pl) using the linked info from the former workbook. This workbook calculates some stuff based on princfull-pl's data
4) Close principles-pl and import stuff from it back into Access.

It's a totally backwards way of doing what I need, but it's the only solution I could find. Anyway, if I go into the directory where both of these workbooks are found and I manually open princfull-pl then principles-pl, then the following code placed into principles-pl works just fine:

Private Sub Workbook_Open()

Workbooks("PrincFULL-PL.xls").Close
Application.Visible=False
ActiveWorkbook.Updatelinks=1
ActiveWorkbook.Save
ActiveWorkbook.Close
Excel.Application.Quit

Basically it immediately closes the source workbook, then makes itself invisible and closes. It's just a backhanded way to force (principles-pl) to update before I reimport it to access.

Like I said, it works perfectly when I do it manually. But if I use an Access macro with two RunApp lines:


excel.exe "\\pathtofile\princfull-pl.xls"
excel.exe "\\pathtofile\principles-pl.xls"

The first file opens, but then I get an "Unable to read file" error as soon as the second file executes (indicating the linked data can't be found), and then it follows up with a Run-time error '9', "Subscript out of range" error.

I've tried changing the code in both the excel VB editor and in the access macro to contain the full path of the file, to only use the filename, to use the .xls extension or not use it, and nothing seems to affect the result. I also tried sticking a pause in my Access macro, thinking maybe the second workbook was opening too soon/simultaneously, but even a 30 second delay gets me the same error.

I've done a bunch of googling and am completely at a loss. Can anyone help before I delete the universe in a fit of rage?

Thanks!

 

[kjv1611]

I doubt that's not your full line of code. If you want help with the line, you need to list the ENTIRE line that you have- every character, unless you need to change a file name, or field name or something to not be giving away private/company information.

My guess is that you're doing it like this:

Application.RunApp .....

That won't work if you're trying to call the Excel application from Access.

What you need to do is create an Excel Application object, then build all your Excel objects off of that, and run your Excel commands off of that.

Dim xlApp as Excel.Application
Dim wb as Excel.Workbook
Dim ws as Excel.Worksheet
Dim r as Excel.Range

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.ActiveWorkbook
Set ws = wb.ActiveSheet
Set r = ws.Range("A1")

Then just use those objects however you see fit.. Of course, the "ActiveWorkbook or Worksheet" only works if that's what you're looking for... if you need to look through workbooks and/or worksheets or needed to specify those values, then of course, do so.

 

[scun]

That actually is the entire code in the Excel workbook. Let me give you some more details though.

The second workbook (princFULL-pl) is deleted and recreated by Access on a regular basis, so I can't write any code into it. Instead, that code is inserted into the permanent calculations workbook (principles-pl).

Ultimately, all I need is for the data in principles-pl to get back to access, and all I want is for it to happen seamlessly. I've got a series of macros (I know...lame, but like I said I'm an idiot) that pull the stuff off. What I was trying to do is have a new macro start whose SOLE purpose is opening princFULL-pl, then principles-pl, and hoping that the code within principles-pl would take care of closing both forms out.

I used the RunApp step within the GUI to do it at first, using this single line:
excel.exe "\\path\princFULL-pl.xls"
excel.exe "\\path\principles-pl.xls"

The first spreadsheet launched fine, but when the second launched, it errored out and the Excel code failed.

So I tried creating a module with the following code:
Option Explicit

Function OpenDocs()

Application.FollowHyperlink "\\PATHTOFILE\PrincFULL-PL.xls"
Application.FollowHyperlink "\\PATHTOFILE\Principles-pl-PL.xls"

End Function

But got the same result. I tried splitting the two lines into different modules and launching the first well before the second, just in case it was a timing thing, but same result. What I really think is going on is that for some reason calling it up through Access is making the second .xls think that the first one isn't even open.

 

[scun]

Sorry, I forgot to change something in my post. When I use that application.followhyperlink command I DON'T get the same result. What happens instead is that the second line runs twice.

So the first spreadsheet opens, then the second, then the excel code runs and they both close--beautiful! But then a message pops up asking if I want to open the second one (again). If I decline, the code halts with an error. If I proceed, the original error is of course duplicated, because this time the source workbook actually isn't open. I have no idea...

 

[kjv1611]

Well, if you want to control the Excel workbook that you're opening, then I suggest you use VBA with the Excel objects I mentioned.

I should have realized you were talking about using Macros rather than direct VBA. I'm sorry - just didn't cross my mind. Makes sense they are shorter, as far as your input.

Another thing to consider -if you just need the data, you can look at using the DoCmd.TransferSpreadsheet function. I think you can use that within the Macros as well, so see what you can find there. So long as you don't have any import errors from grabbing the data, then that method will work fine.

If you have to for sure loop through the cells in Excel, then creating a procedure/function in Access to open the workbook (in a new object instance) and then loop through the cells will be your best bet, I think.

If it goes the route of VBA in the end, I'd suggest opening a thread in the following forum:
forum705