VBA Script in Excel Stalls Out On It's Own After Opening Secondary Excel File
 

Hey everyone,
I'm having a bit of a problem with a bit of VBA code that I've got

going on in an Excel File. Basically, the code that I have set up is
supposed to open a specific file, highlight and copy a range of cells,
the go back to the code's host (excel) file and paste those cells in
there. The problem I'm having is that as soon as I programatically
open up that second file, the script just stops running as if it came
up against the "End sub" command. I've tries eberything that I can
think of, but nothing seems to make any difference. I've posted a bit
of the script below. If any of you can see what I'm doing wrong or
could make some other suggestion it would bve greatly appreciatted.
Thanks.


Code:
'The following code will open the newly created xls file and move all
of the needed data values first to the "Conversion"
'sheet, and then copy the converted data to the "Output" sheet of this
workbook
'Open the newly exported excel file and copy the relevant data
Workbooks.Open Filename:="H:\TeamLead\00u7735\Scripts\ScriptOutpu t\" &
intAgentName & ".xls"
Windows(intAgentName & ".xls").Activate
Sheets(intAgentName).Select
Range("B3:U3").Select
Selection.Copy
'Paste it into this file on the conversino page
Windows("ABC Scripting Automation.xls").Activate
Sheets("Conversion").Select
Range("A2:T2").Select
Selection.Paste
'Copy the converted data to the Final Output page
Range("A13:T13").Select
Selection.Copy
Sheets("Output").Select
'Paste it to a row whose number is based on the counter variable
Range("B" & intCounter + 2 & ":U" & intCounter + 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("A" & intCounter + 2).Select
ActiveCell.Value = intAgentName
'Close the exported Excel file
Windows(intAgentName & ".xls").Activate
ActiveWorkbook.Close
Windows("ABC Scripting Automation.xls").Activate

VBA Script in Excel Stalls Out On It's Own After Opening Secondary Excel File
 

S,
Things to consider...

Do you have events enabled?
Are there any Workbook_Open or Workbook_SheetActivate
events in the workbook?
Could Excel be displaying a dialog box that you don't know about?
That could occur if the workbook requires a password or if the workbook
is "Read Only". The Open method has arguments to cover both of
those situations.
I see you mention the "newly created file". If it was just created,
why is it closed? Just leave it open and you shouldn't have the problem?
What do you mean by "just stops running". Have you stepped thru
the code to try to determine what is happening?

Jim Cone
San Francisco, USA


"SilentThunder"
wrote in message
ups.com...
Hey everyone,
I'm having a bit of a problem with a bit of VBA code that I've got
going on in an Excel File. Basically, the code that I have set up is
supposed to open a specific file, highlight and copy a range of cells,
the go back to the code's host (excel) file and paste those cells in
there. The problem I'm having is that as soon as I programatically
open up that second file, the script just stops running as if it came
up against the "End sub" command. I've tries eberything that I can
think of, but nothing seems to make any difference. I've posted a bit
of the script below. If any of you can see what I'm doing wrong or
could make some other suggestion it would bve greatly appreciatted.
Thanks.


Code:
'The following code will open the newly created xls file and move all
of the needed data values first to the "Conversion"
'sheet, and then copy the converted data to the "Output" sheet of this
workbook
'Open the newly exported excel file and copy the relevant data
Workbooks.Open Filename:="H:\TeamLead\00u7735\Scripts\ScriptOutpu t\" &
intAgentName & ".xls"
Windows(intAgentName & ".xls").Activate
Sheets(intAgentName).Select
Range("B3:U3").Select
Selection.Copy
'Paste it into this file on the conversino page
Windows("ABC Scripting Automation.xls").Activate
Sheets("Conversion").Select
Range("A2:T2").Select
Selection.Paste
'Copy the converted data to the Final Output page
Range("A13:T13").Select
Selection.Copy
Sheets("Output").Select
'Paste it to a row whose number is based on the counter variable
Range("B" & intCounter + 2 & ":U" & intCounter + 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("A" & intCounter + 2).Select
ActiveCell.Value = intAgentName
'Close the exported Excel file
Windows(intAgentName & ".xls").Activate
ActiveWorkbook.Close
Windows("ABC Scripting Automation.xls").Activate