Excel VB Code Error

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am trying to create a .csv file from an Excel workbook
using a VB module and am wondering if someone can tell me
what's wrong with the below code:

Public Sub CSV2()
Application.DisplayAlerts = False
Sheets.Add
ActiveSheet.Name = "Temp_10001"
For Each ws In Worksheets
If ws.Name <> "Temp_10001" Then
ws.UsedRange.Copy
Sheets("Temp_10001").Range("A1").Activate
x = ActiveSheet.UsedRange.Rows.Count
If x > 1 Then ActiveCell.Offset(x, 0).Select
ActiveCell.PasteSpecial (xlPasteValues)
End If
Next
SaveName = Application.GetSaveAsFilename(, "CSV (Comma
delimited)(*.csv), *.csv")
ActiveWorkbook.SaveAs Filename:=SaveName,
FileFormat:=xlCSV
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

It runs fine until it gets to:
SaveName = Application.GetSaveAsFilename(, "CSV (Comma
delimited)(*.csv), *.csv")

Thank you so much.
 
It worked ok for me in xl2002. Well if that line were one long line--not broken
over multiple lines--but I guessed that this was a newsgroup posting line wrap
problem.

Another way to do this kind of thing is to copy the worksheet to a new workbook
and save it from there. Then just close that workbook and get the next
worksheet.

Option Explicit
Sub CSV2A()

Dim newWks As Worksheet
Dim wks As Worksheet
Dim SaveName As Variant

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
SaveName = Application.GetSaveAsFilename _
(filefilter:="CSV (Comma delimited)(*.csv), *.csv")
If SaveName = False Then
'do nothing, skip this one
Else
.SaveAs Filename:=SaveName, FileFormat:=xlCSV
End If
.Parent.Close savechanges:=False
End With
Next wks

MsgBox "done with: " & ActiveWorkbook.Name

End Sub

Maybe xl2002 became less restrictive on that filefilter parm:

If it didn't work, maybe this would be ok:

SaveName = Application.GetSaveAsFilename(filefilter:="Comma delimited, *.csv")
 
Back
Top