![]() |
#1
|
|||
|
|||
![]()
I have a VBA program that reads a comma delimited text file of a Report.
The VBA excludes all report headers, column names, spaces and line feeds leaving only an 80 character string for each report data line. It then parses that string into 8 fields which it then writes out as a comma delimited text file. I then manually import that text file into Excel. Does anyone know of a way that this can be automated. Such as, read the Report file with the VBA code and write the 8 fields into cells 1-8 for each line/row of the report? |
#2
|
||||
|
||||
![]()
Hi tpcervelo,
That's quite easy. Try something based on: Code:
'Note: This code requires a reference to the Excel object model Dim xlApp As New Excel.Application Dim xlWkBk As Excel.Workbook Dim StrWkbk As String, StrWkSht As String, i As Long StrWkbk = "workbook path & filename" ' eg: StrWkbk = "C:\Users\tpcervelo\Documents\Test.xls" StrWkSht = "Sheet Name" Set xlWkBk = xlApp.Workbooks.Open(FileName:=StrWkbk, AddtoMRU:=False) With xlWkBk With .Worksheets(StrWkSht) i = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 .Cells(i, 1).Value = "word field 1" .Cells(i, 2).Value = "word field 2" .Cells(i, 3).Value = "word field 3" .Cells(i, 4).Value = "word field 4" .Cells(i, 5).Value = "word field 5" .Cells(i, 6).Value = "word field 6" .Cells(i, 7).Value = "word field 7" .Cells(i, 8).Value = "word field 8" End With .Save .Close End With xlApp.Quit Set xlWkBk = Nothing: Set xlApp = Nothing
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
Jamal NUMAN | Excel | 12 | 07-09-2018 11:44 PM |
Shared Excel workbook | eguru2 | Excel | 0 | 07-12-2011 12:02 AM |
![]() |
nodari | Outlook | 1 | 05-26-2011 08:51 AM |
![]() |
virsojour | Excel Programming | 5 | 02-01-2011 08:58 PM |
![]() |
KC3K7 | Excel | 1 | 06-04-2009 07:13 AM |