File changing after saving - excel VBA problem
 

I made a macro that formats an excel document. I import information
into excel then format it to export to another company. I made the
macro save the workbook onto the outgoing server as a .csv file. The
other company's system then automatically comes and picks up the saved
workbook and puts it into their system.

Here is my code:


Sub Macro3()
'
' Macro3 Macro
' Macro recorded 5/31/2006 by UnumProvident Corporation
'


'
ActiveWorkbook.SaveAs Filename:= _
"O:\UA\UAna\NA_Outsourcing_Administration\Tenet\Re ports\2006
Weekly reports\full reports\week ending." & Format(Now(),
"mm-dd-yyyy.") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Rows("1:1").Select
Selection.Insert Shift:=xlDown


Columns("N:P").Select
Selection.Delete Shift:=xlToLeft


Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Closed"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown


Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="New"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown


Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="req complete"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown


Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Missing Info"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown


Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Pending"
Cells.Select
Selection.Delete Shift:=xlUp
Columns("I:I").Select
ActiveWorkbook.SaveAs Filename:= _


"B:\Hewitt\TenetHealthCare\NA\LIF\Out\BAS05980.P.E FT.EOIIN.UNUMLIF.csv",

FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False


ActiveWorkbook.SaveAs Filename:= _
"O:\UA\UAna\NA_Outsourcing_Administration\Tenet\Re ports\2006
Weekly reports\reports to server\BAS05980.P.EFT.EOIIN.UNUMLIF" &
Format(Now(), "mm-dd-yyyy") & ".csv", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False


End Sub


This section:


ActiveWorkbook.SaveAs Filename:= _


"B:\Hewitt\TenetHealthCare\NA\LIF\Out\BAS05980.P.E FT.EOIIN.UNUMLIF.csv",

FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False


Is what saves the file to the server to be picked up.


This is what the company says they are getting:


¡µ [áÄ ]


)


_ V B A _ P R O J E C T _ C U R "



úF


dÓþ ú{ dÓþ V B A


"#,##0.00_);ÝRed¨\("$"#,##0.00\) 7 * 2 _("$"* #,##0_);_("$"*
\(#,##0\)
Ó Ù ý


999-99-9999 EE MANUEL-Manuel
SA
a Æ ªÌ ¿ e


d ÙëÿÐMbP?_ * +
%


~


l@Ú


I can not imagine what is causing this. If I look at the file once it
is placed on the server it looks like a normal CSV file. Is there
something I have done wrong in my code that makes this crazyness show
up?


Thanks!
Mary

File changing after saving - excel VBA problem
 

In those portions where you're saving files with an extension of .csv, you're
also telling excel to use a normal workbook fileformat.

.... Format(Now(), "mm-dd-yyyy") & ".csv", FileFormat:=xlNormal, ...

How about telling excel to use xlcsv?

.... Format(Now(), "mm-dd-yyyy") & ".csv", FileFormat:=xlCSV, ...

====
Try opening the resulting files in Notepad to see what the .csv files look
like--don't reopen them in excel.

Dagonini wrote:

I made a macro that formats an excel document. I import information
into excel then format it to export to another company. I made the
macro save the workbook onto the outgoing server as a .csv file. The
other company's system then automatically comes and picks up the saved
workbook and puts it into their system.

Here is my code:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 5/31/2006 by UnumProvident Corporation
'

'
ActiveWorkbook.SaveAs Filename:= _
"O:\UA\UAna\NA_Outsourcing_Administration\Tenet\Re ports\2006
Weekly reports\full reports\week ending." & Format(Now(),
"mm-dd-yyyy.") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Columns("N:P").Select
Selection.Delete Shift:=xlToLeft

Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Closed"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="New"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="req complete"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Missing Info"
Cells.Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Pending"
Cells.Select
Selection.Delete Shift:=xlUp
Columns("I:I").Select
ActiveWorkbook.SaveAs Filename:= _

"B:\Hewitt\TenetHealthCare\NA\LIF\Out\BAS05980.P.E FT.EOIIN.UNUMLIF.csv",

FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

ActiveWorkbook.SaveAs Filename:= _
"O:\UA\UAna\NA_Outsourcing_Administration\Tenet\Re ports\2006
Weekly reports\reports to server\BAS05980.P.EFT.EOIIN.UNUMLIF" &
Format(Now(), "mm-dd-yyyy") & ".csv", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

End Sub

This section:

ActiveWorkbook.SaveAs Filename:= _

"B:\Hewitt\TenetHealthCare\NA\LIF\Out\BAS05980.P.E FT.EOIIN.UNUMLIF.csv",

FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

Is what saves the file to the server to be picked up.

This is what the company says they are getting:

¡µ [áÄ ]

)

_ V B A _ P R O J E C T _ C U R "

úF

dÓþ ú{ dÓþ V B A

"#,##0.00_);ÝRed¨\("$"#,##0.00\) 7 * 2 _("$"* #,##0_);_("$"*
\(#,##0\)
Ó Ù ý

999-99-9999 EE MANUEL-Manuel
SA
a Æ ªÌ ¿ e

d ÙëÿÐMbP?_ * +
%

~

l@Ú

I can not imagine what is causing this. If I look at the file once it
is placed on the server it looks like a normal CSV file. Is there
something I have done wrong in my code that makes this crazyness show
up?

Thanks!
Mary

--

Dave Peterson