importing files from our mainframe into EXCEL

[cincinnati kid]

I have created a file from our mainframe with the ".xls" file extension. By
placing the hexidecimal value '05' between each field on my output file I was
able to cause the columns to be segarated properly.
My question is has anyone done this before? The biggest question is can I
somehow control the width of the cell and set all cells to "text". If I have
leading zeroes in my data EXCEL will suppress it.

 

[Tom Ogilvy]

Give the file a .txt extension. Then
Try turning on the macro recorder while you open the file manually. Walk
through the text import wizard, make appropriate selections. on the last
window, specify each column as text.

Then finish the import and turn off the macro recorder.

No you can use this code to open your files. Just change the file name or
add

fname = Application.GetOpenfileName()

to get the file open dialog and use the result in the opentext method.

 

[gimme_this_gimme_that]

You can generate a VBS script that dynamically creates your Excel
Workbook.

This would get you started.

Yes, like Tom said you'd turn on the recorder to widen column widths,
save that into an importable VBA module. In this example in
YourVBAModule1.bas.



Const DARK_BLUE = 47
Const LIGHT_BLUE = 37


Dim i,objws,objXL,objwb,ObjDomain


Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
ExcelSetUp()
'ExcelWrapUp()

Sub ExcelSetUp()
objXL.StatusBar = "Fetching Data"
objXL.DisplayAlerts = false
Set objwb = objXL.Workbooks.Add
Set objws = ExcelAddSheet("FirstWorkSheet",DARK_BLUE)
For each sheet in objwb.Sheets
if "FirstWorkSheet" <> sheet.name Then
sheet.Activate
sheet.Delete
End If
Next
data = Array ( "head1", _
"head2", _
"head3", _
"head4", _
"head5", _
"head6")
objws.Range(objws.Cells(1,1),objws.Cells(1,15)).Value = data
Set o = ExcelAddSheet("SecondWorkSheet", DARK_BLUE)
Set o = ExcelAddSheet("ThirdWorkSheet", DARK_BLUE)
objws.Activate
End Sub


Public Function ExcelAddSheet(Name,Color)
Set ws = objwb.Sheets.Add
ws.Name = Name
ws.Tab.ColorIndex = Color
Set ExcelAddSheet = ws
End Function


Function GetPath(Path)
ix=InstrRev(Path,"\")
Path=Left(Path,ix)
GetPath = Path
End Function


Sub ExcelWrapUp()
Set oVBC = objwb.VBProject.VBComponents
Set M = oVBC.Import(GetPath() & "\Compare.bas")
M.CodeModule.AddFromString "Public Const FILE1 = " & chr(34) &
FILE1 & chr(34) & chr(10)
M.CodeModule.AddFromString "Public Const FILE2 = " & chr(34) &
FILE2 & chr(34) & chr(10)
Set M = oVBC.Import(GetPath() & "\YourVBAModule1.bas")
Set M = oVBC.Import(GetPath() & "\YourVBAModule2.bas")
objwb.Application.Run "Run.Run" ' run the macro to execute
formatting here.
End Sub