Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA text manipulation 2

Status
Not open for further replies.
HelloWorldGuy

HelloWorldGuy

MIS
Sep 24, 2012
28
US
I have a question in reference to thread705-1379089 I'm trying to format one of the ideas there to what I need.
All I want to do is import the column data from a file once a week. the file has 6 lines of headder that will always be deleted then a repeating three lines that look like:
07/03/12 11:47:15 PAGE 1
UNIT ID ITEM NUMBER LOCATION ONHAND
RESTRUCTURED
every page.

My plan is to have one button that creates the filtered file and then one imports it to the table.I already have the import code. Just getting this new file structured. Thanks ahead of time.

Const gstrQUERYNAME As String = "QUERY NAME*" '1st line of head
Const gstrPagLibrary As String = "LIBRARY NAME*" '2nd line of head
Const gstrFILE As String = "FILE *" '3rd line of head
Const gstrILCMU As String = "ILCMUF04 *" '4th line of head
Const gstrDATE As String = "DATE *" 5th line of head
Const gstrDATENUM As String = "07/03/12 *" '1st line of repeating page head
Const gstrUNIT As String = "UNIT ID *" '2nd line of repeating page head
Const gstrRESTRUCTURED As String = " RESTRUCTURED *" '3rd line of repeating page head

Private Sub cmdExecuteCommand_Click()
On Error GoTo Normalize_File_Error
Dim blnCapture As Boolean
Dim lngFileIn As Long, lngFileOut As Long
Dim strData As String
Dim strBuffer As String

lngFileIn = FreeFile
Open "txtFilePath" For Input As #lngFileIn 'other methods allow user to select input file path
lngFileOut = FreeFile
Open "C:\Filtered.txt" For Output As #lngFileOut
Do
Line Input #lngFileIn, strBuffer
If Trim(strBuffer) = "" Then
'Ignore blank line
ElseIf strBuffer Like gstrQUERYNAME Then
blnCapture = False
ElseIf strBuffer Like gstrPagLibrary Then
blnCapture = False
ElseIf strBuffer Like gstrFILE Then
blnCapture = False
ElseIf strBuffer Like gstrILCMU Then
blnCapture = False
ElseIf strBuffer Like gstrDATE Then
blnCapture = False
ElseIf strBuffer Like gstrPagLibrary Then
blnCapture = False
ElseIf strBuffer Like gstrUNIT Then
blnCapture = False
ElseIf strBuffer Like gstrRESTRUCTURED Then
blnCapture = False
ElseIf blnCapture = True Then
'This is where the data is written to the output file
Print #lngFileOut, strData
End If
Loop Until EOF(lngFileIn)

Normalize_File_Exit:
Reset
Exit Sub

Normalize_File_Error:
'Stop
End Sub
 
Sort by date Sort by votes
You can make your loop a bit simpler
Code: 
Do
    Line Input #lngFileIn, strBuffer
    If Trim(strBuffer) = "" Or strBuffer Like gstrQUERYNAME _
                            Or strBuffer Like gstrPagLibrary _
                            Or strBuffer Like gstrFILE _
                            Or strBuffer Like gstrILCMU _
                            Or strBuffer Like gstrDATE _
                            Or strBuffer Like gstrPagLibrary _
                            Or strBuffer Like gstrUNIT _
                            Or strBuffer Like gstrRESTRUCTURED Then
        [blue]' Do Nothing[/blue]
    Else
        [blue]'This is where the data is written to the output file[/blue]
        Print #lngFileOut, strData
    End If
Loop Until EOF(lngFileIn)

The implication of having gstrDATENUM as a constant means that you will need to modify the program every time you want to read a file for a different date. Perhaps that should be a variable that you can change.
 
Upvote 0 Downvote
data on the files looks like this and all I want is the highlighted. You have a very good point Golom...

QUERY NAME . . . . . CD1234
LIBRARY NAME . . . . ABCD
FILE LIBRARY STRING STRING
IDCNUF04 STRING STRING STRING
DATE . . . . . . . . 07/03/12
TIME . . . . . . . . 11:47:15
07/03/12 11:47:15 PAGE 1
UNIT ID ITEM NUMBER LOCATION MYHAND
RESTRUCTURED
[highlight #FCE94F]R00841859 L 0BR20301000 BINREPL1 100
C1010101 L 05241R62000 C1010101 1,266
C1010101 L 05241R62000 C1010101 1,266
C1010101 L 05241R62000 C1010101 1,266
C1010101 L 05241R62000 C1010101 1,266
Etc.....[/highlight]
07/03/12 11:47:15 PAGE 2
UNIT ID ITEM NUMBER LOCATION MYHAND
RESTRUCTURED
 
Upvote 0 Downvote
I did understand that. Is that not the result that you are getting?
 
Upvote 0 Downvote
With your existing code there are a couple of things that may be happening.

- blnCapture is not being set to TRUE for each new line so, once it is set to FALSE, it is FALSE forevermore and nothing is written.

- I don't see where you are closing the handle for lngFileOut. If you are not closing it then things being written go to an output buffer which is written only periodically to the actual file. Close it with [blue]Close #lngFileOut[/blue]
 
Upvote 0 Downvote
I have updated the code and am still getting the same results. I'm going to attempt to just get the original file to print then begin working backwards to filter the .txt file. Thank you for your help. I'm still looking for an option to deal with the date constant, possibly a string match in the same line...

Code: 
Option Compare Database
Option Explicit

 Const gstrQUERYNAME As String = "QUERY NAME*" '1st line of head
 Const gstrPagLibrary As String = "LIBRARY NAME*" '2nd line of head
 Const gstrFILE As String = "FILE *" '3rd line of head
 Const gstrILCMU As String = "ILCMUF04 *" '4th line of head
 Const gstrDATE As String = "DATE * ' 5th line of head"
 Const gstrDATENUM As String = "07/03/12 *" '1st line of repeating page head
 Const gstrUNIT As String = "UNIT ID *" '2nd line of repeating page head
 Const gstrRESTRUCTURED As String = " RESTRUCTURED *" '3rd line of repeating page head


Private Sub cmdExecuteCommand_Click()
 On Error GoTo Normalize_File_Error
 Dim blnCapture As Boolean
 Dim lngFileIn As Long, lngFileOut As Long
 Dim strData As String
 Dim strBuffer As String
 
lngFileIn = FreeFile
 Open "txtFilePath" For Input As #lngFileIn 'other methods allow user to select input file path
 lngFileOut = FreeFile
 Open "C:\Users\wtm\Desktop\Filtered.txt" For Output As #lngFileOut
 Do
    Line Input #lngFileIn, strBuffer
    If Trim(strBuffer) = "" Or strBuffer Like gstrQUERYNAME _
                            Or strBuffer Like gstrPagLibrary _
                            Or strBuffer Like gstrFILE _
                            Or strBuffer Like gstrILCMU _
                            Or strBuffer Like gstrDATE _
                            Or strBuffer Like gstrPagLibrary _
                            Or strBuffer Like gstrUNIT _
                            Or strBuffer Like gstrDATENUM _
                            Or strBuffer Like gstrRESTRUCTURED Then
        ' Do Nothing
    Else
        'This is where the data is written to the output file
        Print #lngFileOut, strData
        Close #lngFileOut
    End If
Loop Until EOF(lngFileIn)
 
Normalize_File_Exit:
 Reset
 Exit Sub
 
Normalize_File_Error:
 End Sub
 
Upvote 0 Downvote
You have closed #lngFileOut after writing one line. An attempt to write another line will error and, since you don't have anything in the error trap to report the error, the routine will just mysteriously terminate. Move [blue]Close #lngFileOut[/blue] outside the Do Loop.
 
Upvote 0 Downvote
I have updated the code and tried working backwards to even get an exact copy of the file then I was going to try and start filtering out header strings but with no success.
Code: 
Const gstrQUERYNAME As String = "QUERY NAME*" '1st line of head
 Const gstrPagLibrary As String = "LIBRARY NAME*" '2nd line of head
 Const gstrFILE As String = "FILE *" '3rd line of head
 Const gstrILCMU As String = "ILCMUF04 *" '4th line of head
 Const gstrDATE As String = "DATE * ' 5th line of head"
 Const gstrDATENUM As String = "07/03/12 *" '1st line of repeating page head
 Const gstrUNIT As String = "UNIT ID *" '2nd line of repeating page head
 Const gstrRESTRUCTURED As String = " RESTRUCTURED *" '3rd line of repeating page head


Private Sub cmdExecuteCommand_Click()
 On Error GoTo Normalize_File_Error
 Dim blnCapture As Boolean
 Dim lngFileIn As Long, lngFileOut As Long
 Dim strData As String
 Dim strBuffer As String
 
lngFileIn = FreeFile
 Open "txtFilePath" For Input As #lngFileIn 'other methods allow user to select input file path
 lngFileOut = FreeFile
 Open "C:\Users\wtm\Desktop\Filtered.txt" For Output As #lngFileOut
 Do
    Line Input #lngFileIn, strBuffer
    If Trim(strBuffer) = "" Or strBuffer Like gstrQUERYNAME _
                            Or strBuffer Like gstrPagLibrary _
                            Or strBuffer Like gstrFILE _
                            Or strBuffer Like gstrILCMU _
                            Or strBuffer Like gstrDATE _
                            Or strBuffer Like gstrPagLibrary _
                            Or strBuffer Like gstrUNIT _
                            Or strBuffer Like gstrDATENUM _
                            Or strBuffer Like gstrRESTRUCTURED Then
        ' Do Nothing
    Else
        'This is where the data is written to the output file
        Print #lngFileOut, strData
        
    End If
Loop Until EOF(lngFileIn)

Close #lngFileOut

Normalize_File_Exit:
 Reset
 Exit Sub
 
Normalize_File_Error:
 End Sub
 
Upvote 0 Downvote
Why writing strData instead of strBuffer ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Good catch PHV. Nothing like a fresh set of eyes.

It does pay to clean up your code.
Code: 
Private Sub cmdExecuteCommand_Click()
Dim lngFileIn                   As Long
Dim lngFileOut                  As Long
Dim strBuffer                   As String

On Error GoTo Normalize_File_Error
lngFileIn = FreeFile
Open "txtFilePath" For Input As #lngFileIn

lngFileOut = FreeFile
Open "C:\Users\wtm\Desktop\Filtered.txt" For Output As #lngFileOut

Do
    Line Input #lngFileIn, strBuffer
    If Trim(strBuffer) = "" _
       Or strBuffer Like gstrQUERYNAME _
       Or strBuffer Like gstrPagLibrary _
       Or strBuffer Like gstrFILE _
       Or strBuffer Like gstrILCMU _
       Or strBuffer Like gstrDATE _
       Or strBuffer Like gstrPagLibrary _
       Or strBuffer Like gstrUNIT _
       Or strBuffer Like gstrDATENUM _
       Or strBuffer Like gstrRESTRUCTURED Then
        ' Do Nothing
    Else
        'This is where the data is written to the output file
        Print #lngFileOut, strBuffer

    End If
Loop Until EOF(lngFileIn)

Close #lngFileIn
Close #lngFileOut

Reset
Exit Sub

Normalize_File_Error:
MsgBox Err.Number & " - " & Err.Description
End Sub
 
Upvote 0 Downvote
I want to thank both so much for helping me with this! Golom is absolutely right, nothing like new eyes! I have implemented your changes and get a "53 - File not found" error. I have double checked the file paths and my txt box variable name so am unclear as to why this could be.
 
Upvote 0 Downvote
Ok I changed the variable txtFilePath to the actual location of the file and now Filtered.txt prints out in unreadable nonsense. This is truly bizarre.
 
Upvote 0 Downvote
It is almost certainly because you have

Open [red]"[/red]txtFilePath[red]"[/red] For Input As #lngFileIn

I assume that txtFilePath is a global variable containing the path and file name of your file. As you have it however (with quotes around it) the system is looking for a file named txtFilePath. Remove the quotes and, assuming that my guess about txtFilePath is right, it should work.
 
Upvote 0 Downvote
I changed txtFilePath to it's actual path and I tried it without quotes. It's printing stuff like "〰㠶㌶㈴†⁌㐠〱〸㑁〰〰†ㅃㄱ㔰㄰†††ⰶ〲ര唊〰㘶㐲㤳†⁌〠㔰ㄳ㕒〲〰†ㅃㄱ㘰㄰†††ⰲ㜱ര" throughout the page.
 
Upvote 0 Downvote
Can you post a bit of the input file and the "unreadable nonsense" that results from it?

I would make a test file with only a few lines after the headers for testing just to avoid hundreds of lines of unreadable nonsense.
 
Upvote 0 Downvote


Of course:


INPUT DATA


QUERY NAME . . . . . DC31INV
LIBRARY NAME . . . . QGPL
FILE LIBRARY MEMBER FORMAT
ILCMUF04 U_FILES ILCMUF04 ILCMUFFM
DATE . . . . . . . . 07/03/12
TIME . . . . . . . . 11:47:15
07/03/12 11:47:15 PAGE 1
UNIT ID ITEM NUMBER LOCATION ONHAND
RESTRUCTURED
[highlight #FCE94F]R00841859 L 0BR20301000 BINREPL1 100
C1010101 L 05241R62000 C1010101 1,266
C1010201 L 80716901000 C1010201 0
C1010401 L T7299C21000 C1010401 0
U00726618 L 05241R62000 C1010701 1,044
U00725186 L 05603R66000 C1010801 1,580
U00726050 L 13353905000 C1020501 3,000[/highlight]07/03/12 11:47:15 PAGE 1
UNIT ID ITEM NUMBER LOCATION ONHAND
RESTRUCTURED



NONSENSE

儠䕕奒丠䵁⁅‮‮‮‮‮䍄ㄳ义ൖ ††††††††††††††††††††䥌剂剁⁙䅎䕍⸠⸠⸠⸠儠假ൌ ††††††††††††††††††††䥆䕌††††䰠䉉䅒奒†††䕍䉍剅†††䘠剏䅍ൔ ††††††††††††††††††††䱉䵃䙕㐰††唠䙟䱉卅†††䱉䵃䙕㐰††䤠䍌啍䙆് ††††††††††††††††††††䅄䕔⸠⸠⸠⸠⸠⸠⸠⸠〠⼷㌰ㄯല ††††††††††††††††††††䥔䕍⸠⸠⸠⸠⸠⸠⸠⸠ㄠ㨱㜴ㄺവ †††††䕒呓啒呃剕䑅਍げ㠰ㄴ㔸‹䰠†䈰㉒㌰㄰〰‰䈠义䕒䱐‱†††ㄠ〰਍ㅃ㄰㄰㄰†䰠†㔰㐲刱㈶〰‰䌠〱〱〱‱††ㄠ㈬㘶਍ㅃ㄰㈰㄰†䰠†〸ㄷ㤶㄰〰‰䌠〱〱〲‱††††〠਍ㅃ㄰㐰㄰†䰠†㝔㤲䌹ㄲ〰‰䌠〱〱〴‱††††〠਍さ㜰㘲ㄶ‸䰠†㔰㐲刱㈶〰‰䌠〱〱〷‱††ㄠ〬㐴਍さ㜰㔲㠱‶䰠†㔰〶刳㘶〰‰䌠〱〱〸‱††ㄠ㔬〸਍さ㜰㘲㔰‰䰠†㌱㔳㤳㔰〰‰䌠〱〲〵‱††㌠〬〰਍†††††删卅剔䍕啔䕒ൄ
 
Upvote 0 Downvote
OK. Here's what I did. I copied your input to wordpad and saved it as a .txt file. You may actually be looking at a formatted file such as an RTF file that's screwing you up.

Second I messed with your code
Code: 
Private Sub cmdExecuteCommand()
Const gstrQUERYNAME             As String = "QUERY NAME*"    '1st line of head
Const gstrPagLibrary            As String = "LIBRARY NAME*"  '2nd line of head
Const gstrFILE                  As String = "FILE*"          '3rd line of head
Const gstrILCMU                 As String = "ILCMUF04*"      '4th line of head
Const gstrDATE                  As String = "DATE*"          '5th line of head
Const gstrTIME                  As String = "TIME*"          '6th line of head"
Const gstrDATENUM               As String = "07/03/12*"      '1st line of repeating page head
Const gstrUNIT                  As String = "UNIT ID*"       '2nd line of repeating page head
Const gstrRESTRUCTURED          As String = "RESTRUCTURED*"  '3rd line of repeating page head
Dim lngFileIn                   As Long
Dim lngFileOut                  As Long
Dim strBuffer                   As String

On Error GoTo Normalize_File_Error
lngFileIn = FreeFile
Open ".\LineInput.txt" For Input As #lngFileIn

lngFileOut = FreeFile
Open ".\LineOutput.txt" For Output As #lngFileOut

Do
    Line Input #lngFileIn, strBuffer

    If VBA.Trim$(strBuffer) = "" _
       Or strBuffer Like gstrQUERYNAME _
       Or strBuffer Like gstrPagLibrary _
       Or strBuffer Like gstrFILE _
       Or strBuffer Like gstrILCMU _
       Or strBuffer Like gstrDATE _
       Or strBuffer Like gstrTIME _
       Or strBuffer Like gstrPagLibrary _
       Or strBuffer Like gstrUNIT _
       Or strBuffer Like gstrDATENUM _
       Or strBuffer Like gstrRESTRUCTURED Then
        ' Do Nothing
        Debug.Print "DROPPED: " & strBuffer
    Else
        'This is where the data is written to the output file
        Print #lngFileOut, strBuffer
        Debug.Print "PRINTED: " & strBuffer
    End If
Loop Until EOF(lngFileIn)

Close #lngFileIn
Close #lngFileOut

Reset
Exit Sub

Normalize_File_Error:
MsgBox Err.Number & " - " & Err.Description
End Sub
Note that some of your constants had to change because you had a space before the "*" or spaces after it. For example "RESTRUCTURED[highlight #FCE94F] [/highlight]*" is not the same thing as "RESTRUCTURED*" without the space.

When I ran the thing I got
Code: 
R00841859 L 0BR20301000 BINREPL1 100
C1010101 L 05241R62000 C1010101 1,266
C1010201 L 80716901000 C1010201 0
C1010401 L T7299C21000 C1010401 0
U00726618 L 05241R62000 C1010701 1,044
U00725186 L 05603R66000 C1010801 1,580
U00726050 L 13353905000 C1020501 3,00007/03/12 11:47:15 PAGE 1
It appears that there is no line break on the last line between the data and the date/time stamp so it wasn't excluded.
 
Upvote 0 Downvote
I think my constraints are in the wrong place? I have them in General. Sorry I'm new to VBA feel like I'm missing something obvious.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Filter vba problem
Replies
1
Views
441
Andrzejek
Andrzejek
Omarahmed22
  • Locked
  • Question
VBA Formating
Replies
1
Views
375
Andrzejek
Andrzejek
M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
2K
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
280
Moss100
Moss100
TheAceMan1
  • Locked
  • Question
Delete from listi n XL open tab with VBA
Replies
2
Views
315
TheAceMan1
TheAceMan1

Part and Inventory Search

Sponsor

Back
Top