Saving as a worksheet as a CSV file
 

MS has posted an article on this behavior.

http://support.microsoft.com/kb/77295

--
Jim
"AJR" wrote in message
...
|I have a macro that formats a worksheet then saves its as a CSV fiel for
| uploading by another system.
| there are 14 fields some of which are blank. The macro works fine but when
| you review the file in notepad, the commas between the blank cells vanish,
| reapesr later and vanish again. If I save manualy then all commas are
present.
| H,Goods In -
Darlington,DTN,DTNFAC,,,,,,,,,,GID,PO,1,2007-01-29,,LBENTLEY,R
| L,42466106,,00009,1,2007-01-29,,,,,,,,,,,,,,
| L,42475301,,00009,1,2007-01-29,,,,,,,,,,,,,,
| L,42490809,,00009,1,2007-01-29
| L,42490907,,00009,1,2007-01-29
|
| Sub Savefile()
|
| For I = 1 To 2
|
| ChDrive "K:\Rhodes\Test Sleeper\Inbox"
| myFileName = Application.GetSaveAsFilename(filefilter:="CSV Files,
*.csv")
| If myFileName = False Then
| Exit Sub 'user hit cancel
| End If
|
| If Dir(myFileName) = "" Then
| 'do nothing, no existing file
| Else
| resp = MsgBox(Prompt:="That file exists, Overwrite?",
| Buttons:=vbYesNo)
| If resp = vbNo Then
| MsgBox "CSV file not saved--Try again later!"
| Exit Sub
| End If
| End If
| 'Search for range of columns and rows
| On Error Resume Next
| RealLastRow = Range("A:T").Find("*", [A1], , , xlByRows,
xlPrevious).Row
| RealLastColumn = Range("A:T").Find("*", [A1], , , xlByColumns,
| xlPrevious).Column
|
| Set myRng = ActiveSheet.Range("A1", Cells(RealLastRow, RealLastColumn))
| 'Selection
|
| 'create a new workbook with a single sheet
| Set wks = Workbooks.Add(1).Worksheets(1)
|
| myRng.Copy
| wks.Range("a1").PasteSpecial Paste:=xlPasteValues
|
| With wks.Parent
| 'Application.DisplayAlerts = False
| Application.DisplayAlerts = True
| .SaveAs Filename:=myFileName, FileFormat:=CSV
| 'Application.DisplayAlerts = True
| .Close savechanges:=False
| End With
| Next I
| End Sub
|
|
|
|
|
|
|
|
|
| --
| Andy