Access running Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning
I have found a viable method of linking a table to a spreadsheet that works the way I need it to. I have another spreadsheet that takes the output of a query and runs some calculations on it (it involves sequential logic in that if something is GOING to happen, I need to do A. If not, I need to do B.

So, I have an MS Query in Excel that I want to automatically run, save the spreadsheet, and close. I have another table that links to the refreshed data in Excel. (because of the calculations, I am not sure HOW I would do them in Excel, since the records are timed sequentially and must remain in order for the proper decisions to be made). The MS Query is a saved query, referenced in all the cells that require refreshing

I need to open the workbook in Excel, refresh the data, save the workbook and close it (and quit Excel). Since my Access tables are linked, I have no need to manipulate them in Access in manners that I am not aware. Just need to work with Excel stuff

Can someone please help? Thanks in advance
Derek
 
I haven't done the specific thing you're trying (with MS Query in Excel), but I do interface w/ Excel in Access, and it is quite possible to have Access manipulate Excel. You'll need to add a reference to the Excel object library in order to dimension things as Workbooks, etc.

Try something like this

Public Sub RefreshXL(

Dim objXL As Excel.Applicatio
Dim objWB As Workboo

Set objXL = CreateObject("Excel.Application"
Set objWB = objXL.Workbooks.Open("C:\Whatever\Folder\sheet.xls"

objWB.RefreshAll ' I'm not sure if this method does what you need it t

objWB.Sav
objWB.Clos
Set objWB = Nothin

objXL.Qui
Set objXL = Nothin

End Sub
 
Yes, Refreshall works! Thank you

Now, unfortunately, to connect to the datasource (MSQuery - this hits my original database) takes some time, and the save command after refreshall wants to run before the refreshall is completed. So, I need to (and I'm not sure how to do this) put in a delay-type timer for 15 - 20 more seconds. You wouldn't happen to have that trick up your sleeve, now, would you

Thanks in advance
Dere

----- MDW wrote: ----

I haven't done the specific thing you're trying (with MS Query in Excel), but I do interface w/ Excel in Access, and it is quite possible to have Access manipulate Excel. You'll need to add a reference to the Excel object library in order to dimension things as Workbooks, etc.

Try something like this

Public Sub RefreshXL(

Dim objXL As Excel.Applicatio
Dim objWB As Workboo

Set objXL = CreateObject("Excel.Application"
Set objWB = objXL.Workbooks.Open("C:\Whatever\Folder\sheet.xls"

objWB.RefreshAll ' I'm not sure if this method does what you need it t

objWB.Sav
objWB.Clos
Set objWB = Nothin

objXL.Qui
Set objXL = Nothin

End Sub
 
This is a very dirty way to do it, but it's worked for me

intNumSecs = 20 ' Change as neede

datNow = No
datLater = datNo

Do Until DateDiff("s",datNow,datLater) >= intNumSec

datLater = No

Loo

I can never remember how DateDiff works.... you might have to change >= into <= ....you'll know, because if that part is wrong, you'll be in an endless loop

HTH
 
Do you need to have Excel be visible? If this is something that is to happen automatically, then I'd say it would be preferable to have Excel remain invisible. It hasn't impacted my use of Excel, but like I said, I've never used the Refresh method before

Excel 9.0 is 2000, right?

Try this

Set objXL = CreateObject("Excel.Application"
With objX

Set objWB = .Worksheets.Open("C:\Documents and Settings\dwittma\Desktop\putcigs.xls"
objWB.Worksheets(1).QueryTables(1).Refresh(False

intNumSecs = 30 ' Change as neede
datNow = Now(
datLater = datNo
Do Until DateDiff("s", datNow, datLater) >= intNumSec
datLater = Now(
Loo

objWB.Sav
Set objWB = Nothin

.Quit

End Wit

Set objXL = Nothin

Also, what errors do you have



----- Derek Wittman wrote: ----

Your code looks correct (with the >= comparison). However, I cannot seem to get the correct syntax for the refresh statement. The bulk of the code comes from The AccessWeb (Dev Ashish, I think). However, it did not include the refresh line. I am referencing MS Excel 9.0


Private Sub Command16_Click(
DoCmd.Echo Tru
On Error GoTo Command16_Click_Er
DoCmd.DeleteObject acTable, "cigs
DoCmd.TransferSpreadsheet acLink, 8, "cigs", FullPath(), True, Dept() & "!A:E
Dim objXL As Object,
On Error Resume Nex
Set objXL = CreateObject("Excel.Application"
With objXL.Applicatio
.Visible = Tru
.Workbooks.Open "C:\Documents and Settings\dwittma\Desktop\putcigs.xls
.QueryTable.Refresh BackgroundQuery:=Fals
intNumSecs = 30 ' Change as neede
datNow = Now(
datLater = datNo
Do Until DateDiff("s", datNow, datLater) >= intNumSec
datLater = Now(
Loo
.ActiveWorkbook.Sav
End Wit
Excel.Application.Qui
Set objXL = Nothin

Command16_Click_Exit
Exit Su

Command16_Click_Err
MsgBox Error
Resume Command16_Click_Exi
End Su

Any more ideas
Thanks in advance
Dere

----- MDW wrote: ----

This is a very dirty way to do it, but it's worked for me

intNumSecs = 20 ' Change as neede

datNow = No
datLater = datNo

Do Until DateDiff("s",datNow,datLater) >= intNumSec

datLater = No

Loo

I can never remember how DateDiff works.... you might have to change >= into <= ....you'll know, because if that part is wrong, you'll be in an endless loop

HTH
 
hehe... it happens. Now, I'm getting (only intermittently) a General ODBC Runtime Error 1004 from the same line of code

I'm lost. Considering other options (if I knew what they were)... The coding and linking that this will do has the potential to save me about 2 hours for each taxing authority because the formulae are already there. All I need in the end is to open up the 'result' table and record the statistics (10-12 in all) and insert them into an SQL database (manually since more changes have to happen, too)

Thanks again
Dere

----- MDW wrote: ----

Whoops, that should be WorkBOOKS.Open, not Worksheets
 
Unfortinately, we've reached the extent of my knowledge in that arena. At this point, though, I think you've moved out of the realm of this being an Access problem, and you might want to consider posting your current status in an Excel forum

Sorry!
 
I just wanted to thank you, again. I finally got it to work (although I am not sure how). I am running it .visible = true for testing and have confirmed that this works. I suppose I can turn echo off with the same results..

Thanks again
Dere

----- MDW wrote: ----

Unfortinately, we've reached the extent of my knowledge in that arena. At this point, though, I think you've moved out of the realm of this being an Access problem, and you might want to consider posting your current status in an Excel forum

Sorry!
 
Back
Top