CSV - Macro - Template

[Guest]

We have AS400 which outputs a report to a csv daily.
Now you open the csv spreadhseet and the data is right, but the format is
wrong.
Column A too short, Column B needs to be numberic (so we can use a comma in
the 100's), Column C is to short, etc..
We open this daily, do all that formatting just to make it look right. Is
there a way to make a Macro stay in a csv? Any other suggestions? I want to
make this automated if possible.

 

[Guest]

Suggestion:
1) Using Windows Explorer, change the name of the csv file to txt.
Example: Change MyFile.csv to MyFile.txt
2) Start a macro recording and pull up MyFile.txt.
The Text Import Wizard will appear.
3) Follow the Wizard and change data to the appropriate
type such as text
4) Stop the macro recording.

The macro will look something like this...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/7/2005 by Gary_Brown
'

'
Workbooks.OpenText Filename:="D:\Temp\MyFile.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 1))
Cells.EntireColumn.AutoFit
End Sub

5) Change the macro to something like this....
(The only change is manipulation of the file name)
[Assume the file is downloaded to the same folder each day]
[Assume the file name changes daily]

'/=============================================/
Sub GetMyFile()
Dim strFileName As String

strFileName = _
Application.InputBox(Prompt:="Enter File Name: ", _
Default:="MyFile.txt", Type:=2)

Workbooks.OpenText Filename:="D:\Temp\" & strFileName, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 1))
Cells.EntireColumn.AutoFit

End Sub
'/=============================================/


6) Put this macro in your Personal.xls or in a workbook that you will open
daily just prior to pulling in your text file.

HTH,