I have the Auto_Open macro below in a workbook (referred to in my code as
TemplateName) that opens a source CSV file, brings the contents into the
current sheet, does some reorganization, and finally saves it to another CSV
file. for import into another application that cannot handle some of the raw
formatting in the source CSV file.

I have a problem: data has now begun to appear with things like "001"
instead of "1" in some fields in the source CSV file. If I open it in Excel,
the "001" appears as "1", but in Notepad it correctly shows "001". The macro
below ends up converting "001" to "1" in the output CSV file. How can I
ensure that the macro does not convert these numeric-like strings to numbers?


Sub Auto_Open()
Dim DataPath As String
Dim FileName As String
Dim TemplateName As String
Dim ImportFileName As String

DataPath = "J:\HartWorks\TruckMateSupplementals\FleetOne"
TemplateName = "FleetOneTemplate.xls"
ImportFileName = "FleetOneImport.csv"
FileName = "160071.csv"
'
' Copy header into fuel file and save as Excel sheet
'
Workbooks.Open FileName:=DataPath & "\" & FileName
Windows(TemplateName).Activate
Rows("1").Select
Selection.Copy
Windows(FileName).Activate
Range("A1").Select
Selection.Insert Shift:=xlDown
Cells.Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows
Selection.Replace What:="=", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows
Range("C:C,E:E,H:K,Q:Q,S:U,W:X,AA:AM,AN:AQ,AS:AT,A V:AW").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=DataPath & "\" & ImportFileName,
FileFormat:=xlCSV, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.Quit
End Sub

Do the same thing you would do manually:

1. rename the file from .csv to .txt
2. tell the Import Wizard that the field is Text

The file being named .csv shortcircuits the Import Wizard
--
Gary''s Student - gsnu200732


"Brian" wrote:

I have the Auto_Open macro below in a workbook (referred to in my code as
TemplateName) that opens a source CSV file, brings the contents into the
current sheet, does some reorganization, and finally saves it to another CSV
file. for import into another application that cannot handle some of the raw
formatting in the source CSV file.

I have a problem: data has now begun to appear with things like "001"
instead of "1" in some fields in the source CSV file. If I open it in Excel,
the "001" appears as "1", but in Notepad it correctly shows "001". The macro
below ends up converting "001" to "1" in the output CSV file. How can I
ensure that the macro does not convert these numeric-like strings to numbers?


Sub Auto_Open()
Dim DataPath As String
Dim FileName As String
Dim TemplateName As String
Dim ImportFileName As String

DataPath = "J:\HartWorks\TruckMateSupplementals\FleetOne"
TemplateName = "FleetOneTemplate.xls"
ImportFileName = "FleetOneImport.csv"
FileName = "160071.csv"
'
' Copy header into fuel file and save as Excel sheet
'
Workbooks.Open FileName:=DataPath & "\" & FileName
Windows(TemplateName).Activate
Rows("1").Select
Selection.Copy
Windows(FileName).Activate
Range("A1").Select
Selection.Insert Shift:=xlDown
Cells.Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows
Selection.Replace What:="=", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows
Range("C:C,E:E,H:K,Q:Q,S:U,W:X,AA:AM,AN:AQ,AS:AT,A V:AW").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=DataPath & "\" & ImportFileName,
FileFormat:=xlCSV, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.Quit
End Sub

Thanks. That makes sense. I see when I re-recorded the macro that this
results in Array (1,1), Array (2,1), Array (3,2) where the first digit is the
column & the second digit is the format (1 = General, 2 = Text).

Now I just need to use the Name statement to rename the source file from
..csv to .txt. I've done that a lot in Access before, and it looks like VBA is
the same in Excel on this point.

"Gary''s Student" wrote:

Do the same thing you would do manually:

1. rename the file from .csv to .txt
2. tell the Import Wizard that the field is Text

The file being named .csv shortcircuits the Import Wizard
--
Gary''s Student - gsnu200732


"Brian" wrote:

I have the Auto_Open macro below in a workbook (referred to in my code as
TemplateName) that opens a source CSV file, brings the contents into the
current sheet, does some reorganization, and finally saves it to another CSV
file. for import into another application that cannot handle some of the raw
formatting in the source CSV file.

I have a problem: data has now begun to appear with things like "001"
instead of "1" in some fields in the source CSV file. If I open it in Excel,
the "001" appears as "1", but in Notepad it correctly shows "001". The macro
below ends up converting "001" to "1" in the output CSV file. How can I
ensure that the macro does not convert these numeric-like strings to numbers?


Sub Auto_Open()
Dim DataPath As String
Dim FileName As String
Dim TemplateName As String
Dim ImportFileName As String

DataPath = "J:\HartWorks\TruckMateSupplementals\FleetOne"
TemplateName = "FleetOneTemplate.xls"
ImportFileName = "FleetOneImport.csv"
FileName = "160071.csv"
'
' Copy header into fuel file and save as Excel sheet
'
Workbooks.Open FileName:=DataPath & "\" & FileName
Windows(TemplateName).Activate
Rows("1").Select
Selection.Copy
Windows(FileName).Activate
Range("A1").Select
Selection.Insert Shift:=xlDown
Cells.Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows
Selection.Replace What:="=", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows
Range("C:C,E:E,H:K,Q:Q,S:U,W:X,AA:AM,AN:AQ,AS:AT,A V:AW").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=DataPath & "\" & ImportFileName,
FileFormat:=xlCSV, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.Quit
End Sub