Solved: Accessing Already Open Excel Workbook from Word

[AutoData2012]

I'm on office 2007 at work, and I am working on a solution to automate some standard tasks. These tasks involve, web, word, excel and access. I have figured out most of what needs to be done, except for one major issue.

The problem comes in when I download the query results from our web application. The results are downloaded into an excel based document, but the document does not register under the excel.applications. When I review the path and name of the object after it is downloaded they are both HTML references. I have looked for a temporary path to the file but I can't find it actually being saved anywhere.

If it were a simple excel document, I could locate it by name, path or I could easily loop through the Excel.Application object and find it, or use the GetObject command. But none of those work:


'With this the file is never found
For each w in Excel.Application.Workbooks
If w.Name = ExpectedFileName then
Set wbook = w
Exit For
endif
Next w
I have tried using the TASKS collection in word, and I can locate it with a simple loop, but I can't do anything useful once I have found it. I have tried setting a workbook variable to the task, but that always results in the variable being set to nothing:


'Results in wbook being set to nothing
For each T in Tasks
If T.visible = True and T.Name = ExpectedFileName then
Set wbook = T
Exit For
EndIf
Next T
I have considered going old school and using a send keys string with pauses in the appropriate locations to allow the file to be saved. The problem I have with this is that there are many different reports, many different sizes, so waiting a set time will not always work, or be efficient. This is a sloppy solution in my opinion and I would like to avoid it.

Does anyone know of a way, I can access an object, by the Task name, or maybe window name, and set it to a workbook object? Or perhaps to save it, if I could get the document saved to any drive and closed, then I would be able to open it easily.

This is for work, and so I will provide what information I can, but it may be limited. Any help or suggestions would be appreciated.

Eric

[Frosty]

How many excel.exe processes do you have running in task manager when your use of GetObject fails to find the workbook you have open?

[fumei]

For each w in Excel.Application.WorkbooksI hope that is not the actual code tried.

I am not familiar with how web-based files work, but I do know that sometimes (at least in Word), a Word files is NOT loaded into the Word application object. It looks like Word but is actually a browser. Is this possibly happening? That would explain not seeing it in Excel.

[AutoData2012]

Hi Frosty,

After I download the file, I am left with one EXCEL.EXE process running in task manager. I can find the object using the TASKS collection in word, but GetObject fails. The task name is "Microsoft Excel - [Read-Only] [Compatibility Mode]"

Fumei,

That was the code I tried, I thought it would return the workbook collection of the Excel application is that not correct? When I look at the object that is downloaded this is the name and path that it provides (I have changed these slightly for security reasons):

Name - app.loc.com
Path -
"https://app.loc.com/psc/ENV1/EMPLOYEE/HRMS/s/WEBLIB_QUERY.ISCRIPT1.FieldFormula.IScript_ToExcel"

Thank you both for your responses and any help you can provide with this issue.

[Frosty]

I'm not terribly familiar with the tasks collection... And I'm not totally following your specific process, as you're using some fairly generic terms ("I download" "I review").

You have something open, but you can only reference it via the tasks collection. I'm not surprised it can't be set to a workbook object, since it is actually a task object.

It sounds like you're trying to use some kind of embedded excel process (akin to what happens when you have an excel chart object inside a word document).

Is there anyway you can work up some real sample code to demonstrate this process? Otherwise, we're just guessing. And my guess would be-- try to use something in the Task object to save whatever that is as an actual excel file. Then use CreateObject or GetObject to open that excel file in a "real" excel process.

But you either need to provide demo code for this part of the process, or be a lot more explicit on exactly what you (or code or both in combination) are doing an how you're actually doing it.

[fumei]

If what you downloaded is HTML, then it ain't an Excel file.

[AutoData2012]

I have figured it out, I must have had a typo or declaration problem with my variable in the GetObject line previously. But I re-wrote the code and this seems to be working. Sometimes you just have to poke at something until it falls into place.

Set EApp = GetObject(, "Excel.Application")
For Each W In EApp.Workbooks

If W.Name = ExpectedFileName Then
Set Wbook = W
Exit For
End If

Next W
Wbook.SaveAs "H:\tempquery.xls"

Thank you both again for your responses and your help.