1004 Error Message generated when saving .CSV Worksheet

SeaLyon

New Member
Joined
Sep 15, 2011
Messages
28
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 :confused:. 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
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):

SaveAsError.jpg


Does anyone have any ideas on what this error might be any why it is only showing itself outside of the IDE/Debugger?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,130,022
Messages
5,639,600
Members
417,101
Latest member
amoverton2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top