Makro to Run on Open Workbook

[Rob Fenn]

I realise this is probably a very simple question but I would be grateful
for any help offered.

I have a workbook that has formula's linked to another worksheet.
Unfortunately the sheet it is linked to is a csv file so the links will not
update without the sheet being open.

Therefore I wanted to write a makro that opens the csv file and then closes
it again. I have managed to do that with the record Makro command however I
do not know how to get this Makro to run automatically on the worksheet
being opened.


ChDir "H:\SHARED"
Workbooks.Open Filename:="H:\SHARED\susprecpt.csv"
ActiveWindow.Close
ActiveSheet.ShowAllData

Thanks for your help

Rob

 

[Tom Ogilvy]

go into the VBE and click on the ThisWorkbook entry for your project in the
project explorer. Select view code and this should produce the Workbook
level module. In the dropdowns at the top of the module select Workbook
from the left one and Open from the right one.

This should put in the sub declarations:

Private Sub Workbook_Open()

End Sub

Put your code in this procedure

Private Sub Workbook_Open()
' you don't need to Chdir to open the file
'ChDir "H:\SHARED"
Workbooks.Open Filename:="H:\SHARED\susprecpt.csv"
ActiveWindow.Close
If Activesheet.AutoFilterMode = True then
ActiveSheet.ShowAllData
End Sub
End sub

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm