I load a random number of csv files into Excel anywhere from 10 to 50

Excel shows that each csv file is loaded as a different workbook. each
workbook only has one worksheet

I want to execute a sub on each workbook and then write the created sheet and
close the workbook and repeat for the next workbook until I process all loaded
workbooks

The below macro does that but when all the workbooks are processed the while
loop will not end an trys to execute the subroutines on a blank no workbook
screen

How can I get the macro to stop when all the workbooks have been processed?

Thanks for any help.

Dennis

Sub xTBVarItemMxMnBtALL()

'====================================
' 05/26/2007
'Executes the below subs for each loaded workbook
'Sub xTopBotVarITemMxMnBoot()
'Sub WriteSheetandclose()
'===================================
On Error Resume Next
Dim curwk As Worksheet
Dim curwkbk As Workbook

Set curwkbk = ActiveWorkbook
While curwkbk < Null
Call xTopBotVarITemMxMnBoot
Call WriteSheetandClose
Set curwkbk = ActiveWorkbook
Wend
End Sub

Perhaps ActiveWorkbook is not really NULL?? If there were no workbooks open,
then what workbook would host the xTBVarItemMxMnBtALL?

--
Gary''s Student - gsnu200725


" wrote:

I load a random number of csv files into Excel anywhere from 10 to 50

Excel shows that each csv file is loaded as a different workbook. each
workbook only has one worksheet

I want to execute a sub on each workbook and then write the created sheet and
close the workbook and repeat for the next workbook until I process all loaded
workbooks

The below macro does that but when all the workbooks are processed the while
loop will not end an trys to execute the subroutines on a blank no workbook
screen

How can I get the macro to stop when all the workbooks have been processed?

Thanks for any help.

Dennis

Sub xTBVarItemMxMnBtALL()

'====================================
' 05/26/2007
'Executes the below subs for each loaded workbook
'Sub xTopBotVarITemMxMnBoot()
'Sub WriteSheetandclose()
'===================================
On Error Resume Next
Dim curwk As Worksheet
Dim curwkbk As Workbook

Set curwkbk = ActiveWorkbook
While curwkbk < Null
Call xTopBotVarITemMxMnBoot
Call WriteSheetandClose
Set curwkbk = ActiveWorkbook
Wend
End Sub

The Below Works

Sub xTBVarItemMxMnBtALL()

'====================================
' 05/26/2007
'Executes the below subs for each workbook
'Sub xTopBotVarITemMxMnBoot()
'Sub WriteSheetandclose()
'===================================
On Error Resume Next
Dim curwk As Worksheet
Dim curwkbk As Workbook
Dim ii As Integer

ii=0
Set curwkbk = ActiveWorkbook
While ii = 0
Call xTopBotVarITemMxMnBoot
Call WriteSheetandClose
Set curwkbk = ActiveWorkbook
If Len(curwkbk.Sheets(1).Name) = 0 Then Exit Sub
Wend

End Sub

wrote:

I load a random number of csv files into Excel anywhere from 10 to 50

Excel shows that each csv file is loaded as a different workbook. each
workbook only has one worksheet

I want to execute a sub on each workbook and then write the created sheet and
close the workbook and repeat for the next workbook until I process all loaded
workbooks

The below macro does that but when all the workbooks are processed the while
loop will not end an trys to execute the subroutines on a blank no workbook
screen

How can I get the macro to stop when all the workbooks have been processed?

Thanks for any help.

Dennis

Sub xTBVarItemMxMnBtALL()

'====================================
' 05/26/2007
'Executes the below subs for each loaded workbook
'Sub xTopBotVarITemMxMnBoot()
'Sub WriteSheetandclose()
'===================================
On Error Resume Next
Dim curwk As Worksheet
Dim curwkbk As Workbook

Set curwkbk = ActiveWorkbook
While curwkbk < Null
Call xTopBotVarITemMxMnBoot
Call WriteSheetandClose
Set curwkbk = ActiveWorkbook
Wend
End Sub

Since Activeworkbook is an object, you'd want to test with something like:

While Not (curwkbk Is Nothing)



wrote:

I load a random number of csv files into Excel anywhere from 10 to 50

Excel shows that each csv file is loaded as a different workbook. each
workbook only has one worksheet

I want to execute a sub on each workbook and then write the created sheet and
close the workbook and repeat for the next workbook until I process all loaded
workbooks

The below macro does that but when all the workbooks are processed the while
loop will not end an trys to execute the subroutines on a blank no workbook
screen

How can I get the macro to stop when all the workbooks have been processed?

Thanks for any help.

Dennis

Sub xTBVarItemMxMnBtALL()

'====================================
' 05/26/2007
'Executes the below subs for each loaded workbook
'Sub xTopBotVarITemMxMnBoot()
'Sub WriteSheetandclose()
'===================================
On Error Resume Next
Dim curwk As Worksheet
Dim curwkbk As Workbook

Set curwkbk = ActiveWorkbook
While curwkbk < Null
Call xTopBotVarITemMxMnBoot
Call WriteSheetandClose
Set curwkbk = ActiveWorkbook
Wend
End Sub

--

Dave Peterson