File changing after saving - excel VBA problem

  • Thread starter Thread starter Dagonini
  • Start date Start date
D

Dagonini

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\Reports\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.EFT.EOIIN.UNUMLIF.csv",

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


ActiveWorkbook.SaveAs Filename:= _
"O:\UA\UAna\NA_Outsourcing_Administration\Tenet\Reports\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.EFT.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
 
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.
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\Reports\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.EFT.EOIIN.UNUMLIF.csv",

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

ActiveWorkbook.SaveAs Filename:= _
"O:\UA\UAna\NA_Outsourcing_Administration\Tenet\Reports\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.EFT.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
Click to expand...
 
Back
Top