Macro not saving properly? Can't see problem

M

MaryLindholm

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\)
Ó Ù ý

769-10-1114 EE MANUEL-BAILLY
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
 
G

Guest

You have
FileFormat = xlNormal
which meand that it is saving the file as a spreadsheet. When you open it
you are seeing the binary file of the spreadsheet. Try changing that to

FileFormat = xlCSV
--
HTH...

Jim Thomlinson


[email protected] said:
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\)
Ó Ù ý

769-10-1114 EE MANUEL-BAILLY
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

File changing after saving - excel VBA problem 1
Different Results from the Same Macro 2
Macro to save a workbook 1
Bypass Auto_Open 13
Simplify save code 11
Macro should Save and Overwrite file 1
Trying to insert rows in a Macro 5
Complie Syntax Error 6

Top