I'm seeing a strange error when attempting to SAVE the active worksheet as a .CSV file from a VBA macro (Excel 2003 & Excel 2010 running on XP or Win7). The .CSV save code was working fine until I made a series of code updates in other areas of my application (no changes were made to the saving code nor the associated form). Can't see why any of my code changes should have affected the .SaveAs method.
The other odd issue is that this error only occurs when the application is run outside of the VBA IDE. When the code runs within the IDE (& debugger) it works fine, but when it's executed directly in WINDOWS it errors . This complicates trying to gather info on what is causing the error. Here's the routine where the error occurs (this fires from a BUTTON on the associated form):
I'm providing all the gory details of the SUB, but the error is occurring in the first ActiveWorkbook.SaveAs command near the end. Here's the error message that is generated when this app is run from Windows (but again, the error does not fire when running in the IDE):
Does anyone have any ideas on what this error might be and why it is only showing itself outside of the IDE/Debugger?
The other odd issue is that this error only occurs when the application is run outside of the VBA IDE. When the code runs within the IDE (& debugger) it works fine, but when it's executed directly in WINDOWS it errors . This complicates trying to gather info on what is causing the error. Here's the routine where the error occurs (this fires from a BUTTON on the associated form):
Code:
Private Sub butXport_Click()
'Verify that some recipes are selected and complete the XPORT of the
'selected recipe information to a .CSV file.
'LOCAL VARIABLES:
'----------------
Dim bRecipesSelected As Boolean
Dim iCnt As Integer, _
iResp As Integer, _
iNextSel As Integer, _
iPasteRow As Integer, _
iExportNumb As Integer, _
iHour As Integer, _
iMinute As Integer, _
iSecond As Integer
Dim rFound As Range
Dim sExportName As String
bRecipesSelected = False
For iCnt = 0 To (INumbRecipes - 1)
If lstExistingRecipes.Selected(iCnt) = True Then
bRecipesSelected = True
Exit For
End If
Next
If bRecipesSelected = False Then
iResp = MsgBox("Please select at least one RECIPE for export.", _
vbInformation + vbOKOnly, _
"No RECIPES Selected")
Exit Sub
End If
'Set the initial NAME & FOLDER for this export. The format for this name
'is "xxxxx Recipe Export mm-dd-yy [#x]" where x is a number that increases
'with each export.
'Note that the EXPORT sub-folder is created on installation in
'the same folder with the application file.
iExportNumb = Sheets("AppData").Cells(20, 1).Value + 1
Sheets("AppData").Cells(20, 1).Value = iExportNumb
sExportName = ActiveWorkbook.Path & "\Exports\" & "xxxxx Recipe Export " & _
Format(Now, "mm-dd-yy") & " #" & iExportNumb
'Show SaveAs dialog so user can select folder & filename for recipe
'export file.
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Select Destination Folder & Name for Recipe Export File"
.ButtonName = "EXPORT"
.FilterIndex = 15
.InitialFileName = sExportName
'If the user provided an export folder & filename, export selected
'recipes to the provided .CSV file. If this test passes, we are
'guaranteed that at least the USER has provided a valid folder &
'filename for the export file.
If .Show = True Then
sExportFilename = Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)
'Copy the information for the chosen recipes to a TEMP worksheet.
'This is necessary since the Excel CSV output function works only
'for complete worksheets.
Sheets.Add After:=Sheets(Sheets.Count)
'Copy the information for each of the chosen recipes to export, to
'the TEMP worksheet.
With lstExistingRecipes
iPasteRow = 0
'Walk-thru the recipe list in lstExistingRecipes and move export information for
'any selected recipes to the TEMP worksheet.
For iCnt = 0 To (.ListCount - 1)
If .Selected(iCnt) = True Then
'This recipe needs to be EXPORTED. Set ROW VALUE where this info will be pasted
'in the TEMP worksheet.
iPasteRow = iPasteRow + 1
On Error Resume Next
'FIND the recipe's row which contains the data needed for EXPORT.
Set rFound = Sheets("Recipes").Columns(1).Find(What:=lstExistingRecipes.List(iCnt), _
After:=Sheets("Recipes").Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
On Error GoTo 0
'Note that the above FIND is guaranteed to be successful since each selected
'recipe MUST be on the RECIPES sheet.
'Copy RECIPE VALUES to the TEMP sheet.
With Sheets(Sheets.Count)
'RECIPE NAME
.Cells(iPasteRow, 1) = Sheets("Recipes").Cells(rFound.Row, 1)
'Copy rest of RECIPE values. Note that the DESCRIPTION field is for
'Excel use only, so it is not copied.
With Sheets("Recipes")
.Range(.Cells(rFound.Row, 3), .Cells(rFound.Row, 54)).Copy
End With
.Cells(iPasteRow, 2).Select
ActiveSheet.Paste
End With
End If
Next
End With
'Now we have all of the RECIPE information for the set of recipes
'selected by the USER on the TEMP worksheet.
'SAVE the TEMP worksheet (& exported recipe info) in a .CSV file with
'the folder & filename provided by the USER above.
ActiveWorkbook.SaveAs Filename:=sExportFilename, _
FileFormat:=xlCSV, _
CreateBackup:=False
'Delete TEMP worksheet and reset focus on the RECIPES sheet at R1C1.
Sheets(Sheets.Count).Delete
Sheets("Recipes").Select
Sheets("Recipes").Cells(1, 1).Select
'Since the .CSV save above renames the workbook, re-save it as an .XLS
'file with its original name & path.
ActiveWorkbook.SaveAs Filename:=SWorkbookNameFolder, _
FileFormat:=xlExcel8, _
CreateBackup:=False
iResp = MsgBox("Recipe Export File Created.", _
vbInformation + vbOKOnly, _
"Export Completed")
Unload Me
End If
End With
End Sub
Does anyone have any ideas on what this error might be and why it is only showing itself outside of the IDE/Debugger?