WmHumphrey
Technical User
Can anyone illuminate me as to why my macro is hanging when trying to copy a chart sheet (a 3D contour plot), and the worksheet which contains its data, to a new workbook? The chart sheet also contains several text boxes whose text will be modified programmatically later in the macro.
Here's the code... I checked the locals window and all variables are declared and have valid values as of the line before the one that hangs.
' Create new plot template
Workbooks.Add Template:=strTemplateDir & "\" & strPlotTemplateName
'Adjust plot template name to new workbook's
strPlotTemplateName = Workbooks(Workbooks.Count).Name
' Create new workbook to build data summary in; save in working directory
Workbooks.Add Template:=xlWBATWorksheet
Workbooks(Workbooks.Count).SaveAs FileName:=strWorkDir & "\" _
& strWorkFileName
'Loop through rows in DB file
Do
'Open raw data file for current row in DB file
strRawDataFile = Workbooks(strDBFileName).Sheets(1).Cells(intDBRow,10).Value
Workbooks.OpenText FileName:=strRawDataFile, DataType:=xlDelimited, _
Tab:=True, origin:=xlWindows
'Strip path from raw data file name
strRawDataFile = Workbooks(Workbooks.Count).Name
'Read pattern type from DB file
strPatternType = Workbooks(strDBFileName).Sheets(1).Cells(intDBRow, 11).Value
strPatternTemplateName = strPatternType & " Pattern"
'Copy appropriate pattern and raw data form to temp workbook
'*** HERE'S THE LINE THAT HANGS MY MACHINE ***
Workbooks(strPlotTemplateName).Sheets(Array(strPatternTemplateName, _
"Raw Data").Copy Before:=Workbooks(strWorkFileName).Sheets(1)
'... (more code, modifying the newly copied "Raw Data" sheet and the text boxes
'on the chart, follows but the macro never gets past the line above!)
intDBRow = intDBRow + 1
Loop Until Workbooks(strDBFileName).Sheets(1).Cells(intDBRow,1).Value = ""
If anyone has ANY ideas, please help!
I've got Excel 97 SR-1 on a 400MHz P-II with 128 MB and Windows 95-B (and no control over updates).
Thanks in advance!
Bill
Here's the code... I checked the locals window and all variables are declared and have valid values as of the line before the one that hangs.
' Create new plot template
Workbooks.Add Template:=strTemplateDir & "\" & strPlotTemplateName
'Adjust plot template name to new workbook's
strPlotTemplateName = Workbooks(Workbooks.Count).Name
' Create new workbook to build data summary in; save in working directory
Workbooks.Add Template:=xlWBATWorksheet
Workbooks(Workbooks.Count).SaveAs FileName:=strWorkDir & "\" _
& strWorkFileName
'Loop through rows in DB file
Do
'Open raw data file for current row in DB file
strRawDataFile = Workbooks(strDBFileName).Sheets(1).Cells(intDBRow,10).Value
Workbooks.OpenText FileName:=strRawDataFile, DataType:=xlDelimited, _
Tab:=True, origin:=xlWindows
'Strip path from raw data file name
strRawDataFile = Workbooks(Workbooks.Count).Name
'Read pattern type from DB file
strPatternType = Workbooks(strDBFileName).Sheets(1).Cells(intDBRow, 11).Value
strPatternTemplateName = strPatternType & " Pattern"
'Copy appropriate pattern and raw data form to temp workbook
'*** HERE'S THE LINE THAT HANGS MY MACHINE ***
Workbooks(strPlotTemplateName).Sheets(Array(strPatternTemplateName, _
"Raw Data").Copy Before:=Workbooks(strWorkFileName).Sheets(1)
'... (more code, modifying the newly copied "Raw Data" sheet and the text boxes
'on the chart, follows but the macro never gets past the line above!)
intDBRow = intDBRow + 1
Loop Until Workbooks(strDBFileName).Sheets(1).Cells(intDBRow,1).Value = ""
If anyone has ANY ideas, please help!
I've got Excel 97 SR-1 on a 400MHz P-II with 128 MB and Windows 95-B (and no control over updates).
Thanks in advance!
Bill