Open File --- macro stops

Posted by Zevi on May 01, 2001 9:34 PM

I have several files I wanted to do the same macro that I've made. The macro is saved in a seperate file. It works fine if I open the file manualy first then run the macro afterwards.

1. I wanted the macro to open the file automatically and run the rest of the macro. Everytime the macro open the file then it stops. If I debug with F8 it seems OK though (not stop). This is the macro:
ChDir "Mydir"
Workbooks.Open "filename1"
Windows(filename1).Activate
For i = 1 To 33
Sheets(i).Select
....Solver program here

2. I have "filename1" to "filename20" to repeat, how do I program the macro to do that?

Thanks in advance.
Zevi

Posted by Dave Hawley on May 01, 2001 11:30 PM

Hi Zevi

For the macro to fire automatically you would need to place it in the Module of "ThisWorbook". To get there, right click on the sheet picture, top left next to "File" and select "View Code". Then place in some code like this:

Private Sub Workbook_Open()
Dim iFile As Integer
ChDir "Mydir"
For iFile = 1 To 20
Workbooks.Open "filename & str(iFile)"
Windows(filename1).Activate
For i = 1 To 33
Sheets(i).Select
'Your code
Next i
Next iFile

End Sub

OzGrid Business Applications

Posted by Zevi on May 02, 2001 8:20 AM

File End Sub

Hi Dave,

Thanks a lot for your code, however there is an error (run-time error 9 - subscript out of range) when the macro execute

Windows(filename).Activate

This doesn't happen when I put the code in "Module" instead in ThisWorkbook. Other advise?

Thanks,
Zevi

Posted by Dave Hawley on May 03, 2001 4:47 AM

Zevi

This is most likely because you have your String variable "filename1" dimensioned at a Module or Procedure level only. Use:

Public filename1 as String

You MUST place this at the top of a NORMAL module.

Dave
OzGrid Business Applications