Excel and vba

JackieChan

Guys,
I'm new to Excel vba and need to get a little help with issue

I have an excel document with an external data source.
I want to process this data automatically when the document is opened.
I placed the code in the Auto_Open() sub routine but when this sub kicks off the external data has not loaded at that stage.
I've read about with a Workbook_Open routine but I can't get this to work.
Can anyone advice how to do what I'm trying to do?
JC

cyberbob

if ( and thats a big if ) im reading you right you probably just need application.calculate on top of your auto_open() sub

if that doesnt do the trick you may need to use the excel4macro to read in values from your external source , eg :
val= ExecuteExcel4Macro("'C:\Documents and Settings\Bob\Desktop\[book1.xls]sheet1'!R1C1") and populate them in your workbook . This is a little cumbersome, but handy for getting data from closed files.

david powell

Hi,

I've a bit of experience of VBA and excell.....

What is your external data source you are trying to pull data from?? Is it some sort of windows dll? text file? com-port?

Any code placed in:-

Private Sub Workbook_Open()

End Sub

Will execute when the workbook is opened...

JackieChan

Guys thanks for your advice.
I'm running Excel 2000 if that makes any difference.
My Workbook_Open function is not been called.
I have placed a msgbox statement within it and its not displaying.

My file is a simple .txt file that I have set up with the External data source option.I need this data to be imported before I do any more processing(charts are using this data)
I'll look in to the ExecuteExcel4Macro method also.

JackieChan

I moved the code that I had from a Module to the code for "ThisWorkbook" and the Workbook_Open event fired when I opened the file.

The problem is that the external data still has not been imported at the time the event fires.

JackieChan

I got around this by loading the external file programatically using
"With ActiveSheet.QueryTables.Add(Connection...."

cyberbob

JackieChan wrote:

My Workbook_Open function is not been called.
I have placed a msgbox statement within it and its not displaying.

I'll look in to the ExecuteExcel4Macro method also.

sounds like a security settings problem !

oh and i doubt the executeexcel4macro will be any good for your text files