Wait function which allows other Excel action

  • Thread starter Thread starter John Gilchrist
  • Start date Start date
J

John Gilchrist

I am loading two excel workbooks.
I want to run a macro from the first workbook, but I don't want to complete
the macro code until the other workbook has finished loading

I am restricted from placing macros in the 2nd workbook.

Is there some type of wait function which can allow loading of the 2nd
workbook while the wait is in progress?

Thanks,
john G.
 
John Gilchrist said:
I am loading two excel workbooks.
I want to run a macro from the first workbook, but I don't want to complete
the macro code until the other workbook has finished loading

I am restricted from placing macros in the 2nd workbook.

Is there some type of wait function which can allow loading of the 2nd
workbook while the wait is in progress?
Click to expand...

I assume you're not opening the second workbook from a macro in the first
workbook. You could hack something like


On Error Resume Next
Do
Err.Clear
Application.Goto _
Reference:=Workbooks("other.xls").Worksheets(1).Range("A1")
Loop While Err.Number <> 0
Me.Activate
MsgBox "2.xls loaded"


since you can't goto a cell in another workbook unless it's fully loaded.
 
If you're opening that other workbook in the same instance of excel, excel will
load your file before continuing with the rest of your code.

Have you noticed a different behavior?
 
Yes, here are my symptoms
I want to copy and paste some cells from the 2nd workbook, but I don't know
the name of the 2nd workbook. I tried to switch to the other workbook, by
using
Windows(2).activate 'assuming that 1st window is 1st loaded and 2nd is
2nd loaded
This worked if I ran macro after both are loaded, but
Windows(2) activate seems to activate the 1st window when I run macro
from 1st during startup
I tried inserting a for/next loop delay with same results
My assumption was that macro was completing before loading 2nd workbook
(creating 2nd window)

Hope this makes sense - I appreciate your help
John
 
Perhaps I am doing this the hard way. - my basic problem is this:
I have a comma-separated variable text file, which I want to process with an
excel macro amd display the numerical data as a chart.

Since these many DATA.CSV files are externally created, I cannot embed any
macros in these file.
I planned to have my client start Excel from the command line and open any
desired DATA.CSV file. I then inserted my processing file (with embedded
Auto_Open macro) macro in the XLSTART folder, so that the processing file
opens automatically along with the data file.
I figured that the Auto_Open macro would execute automatically, and process
the data.

This works OK, except that I am having trouble switching from the processing
file (Coil_Viewer.xls) to the DATA.CSV file to copy the raw data to the
clipboard.

I tried switching between files, using WINDOWS(2).ACTIVATE as described in
my other posted response with no luck.

Thanks for your help.
John
 
When I do this kind of thing, I'll distribute a file that does the import of the
text file. It usually has two worksheets in it. (And as many hidden worksheets
as I need to make my life easier.)

The first worksheet is instructions and history.

The second worksheet is a giant button from the Forms toolbar that's assigned to
a macro that opens the text file and does what I want.

(The hidden worksheets are for my macros benefit only.)

If the name of the text file is always the same, you can just open it (if it
exists). If the name can vary, you can let the user point at the file and open
the one they want.

I record a macro that imports the file (usually *.txt so I have more control
over the field types). I add the headers, filters, page setup, etc. You could
create the charts at the same time.

I like to use a variable to represent that CSV workbook (and even the CSV
worksheet). Then I don't have to worry about the name of the workbook or name
of the window.

I can refer to the worksheets within the workbook with the button by using
"ThisWorkbook" and get to the CSVWks like:

Option Explicit
Sub testme01()

Dim CSVWks As Worksheet
Dim CSVWkbk As Workbook
Dim myFileName As Variant

myFileName = Application.GetOpenFilename("CSV Files, *.csv")

If myFileName = False Then
'user hit cancel
Exit Sub
End If

Workbooks.Open Filename:=myFileName

Set CSVWks = ActiveSheet
'if you really need the workbook
Set CSVWkbk = CSVWks.Parent

CSVWks.Rows(1).Insert

ThisWorkbook.Worksheets("Headers").Rows(1).Copy _
Destination:=CSVWks.Range("a1")

End Sub

Then I continue my processing and either allow the user to save the new workbook
or even save it in code.

I find that that most people can handle clicking on a giant button and saving
their work.

It seems lots easier to me.
 
John,

If you have Excel 2000-up, you may want to import the file into the workbook
with the macro. Then the macro need only refresh it next time you want to
re-read the file. Neat and clean. Read up on it at
www.smokeylake.com/excel/textfiles.htm

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

John Gilchrist said:
Perhaps I am doing this the hard way. - my basic problem is this:
I have a comma-separated variable text file, which I want to process with
an
excel macro amd display the numerical data as a chart.

Since these many DATA.CSV files are externally created, I cannot embed
any
macros in these file.
I planned to have my client start Excel from the command line and open any
desired DATA.CSV file. I then inserted my processing file (with embedded
Auto_Open macro) macro in the XLSTART folder, so that the processing file
opens automatically along with the data file.
I figured that the Auto_Open macro would execute automatically, and
process
the data.

This works OK, except that I am having trouble switching from the
processing
file (Coil_Viewer.xls) to the DATA.CSV file to copy the raw data to the
clipboard.

I tried switching between files, using WINDOWS(2).ACTIVATE as described in
my other posted response with no luck.

Thanks for your help.
John
Click to expand...
 
Back
Top