Auto Open file VBA Refresh MS Query Save Close
 

I have made a MS Query in Excel that queries a DB and puts the info int
excel. Is there code so that if I schedule the file to open the cod
will let the query to refresh and save and close the file?

Now we are having an employee open the file click enable auto refres
and save and close the file.

Thanks,
Greg
Impulse Manufacturin

--
gcutte
-----------------------------------------------------------------------
gcutter's Profile: http://www.excelforum.com/member.php...fo&userid=2751
View this thread: http://www.excelforum.com/showthread.php?threadid=47029

Auto Open file VBA Refresh MS Query Save Close
 

Greg
Try something like this (you'll have to change it for your setup -
easiest way will be to record a macro then refresh your query then stop
and paste the code)

put this in the 'ThisWorkbook' class module:

Private Sub Workbook_Open()
Sheet1.QueryTables(1).Refresh
Me.Save
Me.Close savechanges:=False
End Sub

note the workbook closes before the code finishes which isn't very
elegant, but it gets the job done.


cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=470298

Auto Open file VBA Refresh MS Query Save Close
 

HEY, if you put worksheet.close code in teh worksheet open sub.......

The only way to open the file for viewing os to disable macros when asked at
open.

"Simon Murphy" wrote:


Greg
Try something like this (you'll have to change it for your setup -
easiest way will be to record a macro then refresh your query then stop
and paste the code)

put this in the 'ThisWorkbook' class module:

Private Sub Workbook_Open()
Sheet1.QueryTables(1).Refresh
Me.Save
Me.Close savechanges:=False
End Sub

note the workbook closes before the code finishes which isn't very
elegant, but it gets the job done.


cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=470298