Our business system prints a month end GL report that we save in text format instead of printing. I wrote a Visual Basic program a few years back that reads the text file, excludes the report headers and line feeds and parses the 80 character record into 8 fields writing out a comma delimited text file That file is opened in Excel and saved as a workbook. I want to automate the process so the user only has to open the Excel file, answer the name of the input file and have an Excel VBA modue convert the text file as my VB code did. Following is the control portion off the VB program.
[code]
BeginProc:
On Error GoTo ErrorHandler
FileOpen(1, InpSourceFile, OpenMode.Input)
FileOpen(2, OutPutFile, OpenMode.Output)
Do
Input(1, strRec)
strRec = RTrim(strRec)
If VB.Left(strRec, 1) = Chr(12) Then GoTo NxtRec
If VB.Left(strRec, 10) = "Run Date: " Then GoTo NxtRec
If VB.Left(strRec, 10) = "Inc. " Then GoTo NxtRec
If VB.Left(strRec, 10) = " " Then GoTo NxtRec
If VB.Left(strRec, 10) = "J C - - " Then GoTo NxtRec
If VB.Left(strRec, 10) = "Ref ----" Then GoTo NxtRec
If VB.Left(strRec, 10) = "Job # Pa" Then GoTo NxtRec
If VB.Left(strRec, 10) = "Account#: " Then strAcct = Mid(strRec, 11, 21)
If Len(strRec) < 80 Then GoTo NxtRec
strJobnum =VB.Left(strRec, 6)
strPagenum = Mid(strRec, 7, 7)
strHrsDebit = Mid(strRec, 14, 10)
strCostDebit = Mid(strRec, 24, 13)
strHrsCredit = Mid(strRec, 38, 10)
strCostCredit = Mid(strRec, 48, 13)
strCostCat = Mid(strRec, 69, 41)
WriteLine(2, strAcct, strJobnum, strPagenum, strHrsDebit, strCostDebit, strHrsCredit, strCostCredit, strCostCat)
strRcnt = strRcnt + 1
NxtRec:
strRec = ""
Loop Until EOF(1)
[code/]
If anyone can direct me to code samples or examples I would appreciate it.
thanks...