![]() |
|
#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 | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
How to write the degree, minute second format in excel
|
Jamal NUMAN | Excel | 12 | 07-09-2018 11:44 PM |
| Shared Excel workbook | eguru2 | Excel | 0 | 07-12-2011 12:02 AM |
Outlook2003 - how to write attached file to folder
|
nodari | Outlook | 1 | 05-26-2011 08:51 AM |
macro to transfer data from one workbook to another workbook
|
virsojour | Excel Programming | 5 | 02-01-2011 08:58 PM |
Create a "report" in MS Word from an Excel file
|
KC3K7 | Excel | 1 | 06-04-2009 07:13 AM |