Microsoft Office Forums Read text Report file with VBA and write parsed fields to Excel workbook

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-04-2012, 12:48 PM
tpcervelo tpcervelo is offline Read text Report file with VBA and write parsed fields to Excel workbook Windows XP Read text Report file with VBA and write parsed fields to Excel workbook Office 2003
Novice
Read text Report file with VBA and write parsed fields to Excel workbook
 
Join Date: Sep 2010
Posts: 5
tpcervelo is on a distinguished road
Default Read text Report file with VBA and write parsed fields to Excel workbook

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?
Reply With Quote
  #2  
Old 01-05-2012, 10:14 PM
macropod's Avatar
macropod macropod is offline Read text Report file with VBA and write parsed fields to Excel workbook Windows 7 64bit Read text Report file with VBA and write parsed fields to Excel workbook Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Read text Report file with VBA and write parsed fields to Excel workbook 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
Read text Report file with VBA and write parsed fields to Excel workbook Outlook2003 - how to write attached file to folder nodari Outlook 1 05-26-2011 08:51 AM
Read text Report file with VBA and write parsed fields to Excel workbook macro to transfer data from one workbook to another workbook virsojour Excel Programming 5 02-01-2011 08:58 PM
Read text Report file with VBA and write parsed fields to Excel workbook Create a "report" in MS Word from an Excel file KC3K7 Excel 1 06-04-2009 07:13 AM


All times are GMT -7. The time now is 01:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft