Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing files from our mainframe into EXCEL
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing files from our mainframe into EXCEL
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. -- Regards, Tom Ogilvy "cincinnati kid" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing files from our mainframe into EXCEL
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)).Va lue = 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CVS files into Excel | Excel Discussion (Misc queries) | |||
Importing a Value from many Excel Files | Excel Discussion (Misc queries) | |||
Importing CSV files into Excel | Excel Discussion (Misc queries) | |||
importing files into excel | Excel Discussion (Misc queries) | |||
Excel VBA-Importing txt files with VBA | Excel Programming |