Prompt for and then fix a TXT file

Topic

New Reply

I get a text file similar to the one attached except considerably more rows.
I need a macro within Excel that will prompt for the text file (browse and click) and then look through the first field.
Any row entry in field 1 that is a number is left alone; any entry that has a letter anywhere in field 1 has the 3rd field changed to have a “0” (a zero) placed to the left of the third digit (becoming the fourth digit).

So, the next to last entry in the attachment is changed so that the 3rd field becomes 30260 and the last entry is change to become 130260.

Probably the resulting text file should be named the same as the input file with some designation after the source file name like “Reformatted”

Is that a piece of cake?

TIA

Kevin

Reply | Quote

Replies

Kweaver

The solution I propose does not use a Macro but its fast and should meet your needs.

Attached in the Workbook “Input2.xlsx” It has been set up to be the master Workbook.

What you would do is first import the text file into an Excel workbook and change the Worksheet name to what you want it to be.

Next Open the Input2.xlsx workbook and copy the renamed worksheet into the master Workbook
Then on the “Revised Data” Worksheet in Cell H3 type in the exact name of the imported worksheet.
You will now have the data revised as you requested. If you need it be “Flat” just copy and paste special “Values”

Hope this helps.

TD

Reply | Quote

Thanks, duthiet. That’s too many steps for the user. They want to click a button and have a macro navigate to the specific folder and automatically make the changes as I noted. If I were doing this for them over and over again, I could easily do what you’ve suggested, but the user needs it to be automatic. Actually, even if I were doing it for them, I’d want an automatic macro to do it for me as well.

Reply | Quote

Try this:

Place in ThisWorkbook Module:

Code:

Private Sub Workbook_Open()
    Application.WindowState = xlMinimized
    OpenFile
End Sub

Place in a standard module:

Code:

Public Sub OpenFile()
Application.ScreenUpdating = False
[COLOR=”#008000″]’——————————————
‘DECLARE VARIABLES[/COLOR]
Dim Filter As String
Dim Caption As String
Dim Fname As String
Dim CurPath As String
[COLOR=”#008000″]’——————————————
‘SET VARIABLES[/COLOR]
Filter = “Text files (*.txt),*.txt”
Caption = “Please Select the source file ”
CurPath = CurDir     ‘GET CURRENT DIRECTORY
ChDir (“C:UsersMaudibeDesktop”)
[COLOR=”#008000″]’——————————————
‘OPEN TEXT FILE[/COLOR]
Fname = Application.GetOpenFilename(Filter, , Caption)
Workbooks.OpenText Filename:=Fname, Origin:=437, StartRow:=1, DataType:=xlDelimited, Tab:=True, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
[COLOR=”#008000″]’——————————————
‘CHECK FOR TEXT VALUES AND INSERT ZERO[/COLOR]
For I = 1 To LastRow
    If Not IsNumeric(Cells(I, 1)) Then
        Num1 = Left(Cells(I, 3), 2)
        Num2 = Right(Cells(I, 3), Len(Cells(I, 3)) – 2)
        Cells(I, 3) = Num1 & “0” & Num2
    End If
Next I
[COLOR=”#008000″]’——————————————
‘SAVE AND CLOSE FILES[/COLOR]
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.Quit
End Sub

I purposely did not code error handling so that there would be a way to open the workbook. Just click cancel when browsing for the text file.

Reply | Quote

Maudibe, that’s heading in the right direction.

But, I’m trying to avoid any user requirements to change the code (e.g., I don’t want them to have to modify the chDir line).
Is there a way to prompt and browse for the text file without having it launch onOpen? Then, fix the text file but save it under a new name similar to the old? If the original is Input1, maybe the new, changed could be Input1-Edited.

BTW: the 3rd field could be a multi-digit number, not always 4 or 5 digits. But the change is always to the left of the 3rd digit from the right. So, 12345 becomes 120345 and 999555 becomes 9990555. Is this valid: Num1 = Left(Cells(I, 3), Len(Cells(I, 3)) – 3)

/Kevin

Reply | Quote

HELP, Maud or someone!

This now prompts now, but I need to adjust the Num1 and Num2 lines because they’re no longer in the sheet.

Code:

Sub ReformatFile0()

    Dim FileIn    As String
    Dim FileOut   As String
    Dim sLine     As String
    Dim sRecord() As String
    Dim LastRow   As Integer
    Dim Num1      As Integer
    Dim Num2      As Integer
    Dim Data      As Range
    Dim myrng     As Range
    Dim i         As Integer
    Dim j         As Integer
    Dim lineText  As TextRange2
    

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Filters.Add "Text Files", "*.txt"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "File open aborted"
            Exit Sub
        End If
        FileIn = .SelectedItems(1)
    End With
    
    If FileIn  "" Then
    
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    If Not IsNumeric(Cells(i, 1)) Then
        Num1 = Left(Cells(i, 3), Len(Cells(i, 3)) - 3)
        Num2 = Right(Cells(i, 3), 3)
        Cells(i, 3) = Num1 & "0" & Num2
    End If
Next i

    
    ' filename = ThisWorkbook.Path & "textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"
    ' LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    FileOut = Left(FileIn, Len(FileIn) - 4) & " - Reformatted.txt"
    Open FileOut For Output As #1
    Data = Range("A1:G" & LastRow)
    Set myrng = Range("data")
    
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
            lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
        Next j
        Print #1, lineText
    Next i
    
    Close #1
    MsgBox "Finished"
        
    End If  'FileIn ...
    
End Sub 'ReformatFile0()

Reply | Quote

Maud: Thanks. I posted my nonsense before realizing you did a slick solution. Thanks!!

Reply | Quote

any entry that has a letter anywhere in field 1 has the 3rd field changed to have a “0” (a zero) placed to the left of the third digit (becoming the fourth digit).

But the change is always to the left of the 3rd digit from the right

Confusing. Here is the revised file that will not open minimized. Click on the button to run the code. The Open Dialogue box will open to the C:Users folder (>= Win 7) which does not need user intervention. If XP then change the default folder to what you want. Navigate to the folder with the text file then open it. The conversion will take place followed by a message stating the conversion is complete with the new file name and path. If a zero is added, it will be the fourth digit from the right.

Reply | Quote

Maud…

I seem to have gotten this to work if I paste the text file data into a sheet and run this macro.
Would be nice to be able to browse for it.

Code:

Sub Insert0()
'
' Insert0 Macro
'
' Dim filename As String, lineText As String
' Dim myrng As Range, i, j
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
    If Not IsNumeric(Cells(i, 1)) Then
        Num1 = Left(Cells(i, 3), Len(Cells(i, 3)) - 3)
        Num2 = Right(Cells(i, 3), 3)
        Cells(i, 3) = Num1 & "0" & Num2
    End If
Next i

    
    filename = ThisWorkbook.Path & "textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"
    ' LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Open filename For Output As #1
    Data = Range("A1:G" & LastRow)
    Set myrng = Range("data")
    
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
            lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
        Next j
        Print #1, lineText
    Next i
    
    Close #1
End Sub

Reply | Quote

Kevin,

Here’s a slightly different approach.

Code:

Option Explicit

Function GetFile() As String

   Dim intChoice As Integer
   Dim strPath   As String
   Dim vParts    As Variant
   Dim iCntr     As Integer
   
'only allow the user to select one file
   Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
   
'make the file dialog visible to the user
   intChoice = Application.FileDialog(msoFileDialogOpen).Show
   
'Retrieve & Return Fully Qualified File Spec to the Caller
   If intChoice  0 Then
     'get the file path selected by the user
     strPath = Application.FileDialog( _
               msoFileDialogOpen).SelectedItems(1)
     GetFile = strPath
   Else
     GetFile = ""
   End If
   
End Function  'GetFile

Sub ProcessFile()

   Dim zFileSpec As String
   Dim wkbMain   As Workbook
   Dim wkbRaw    As Workbook
   Dim lCntr     As Long
   Dim zTemp     As String
   
   Set wkbMain = ActiveWorkbook
   
   zFileSpec = GetFile()
   
   If (zFileSpec  "") Then
     Workbooks.OpenText Filename:=zFileSpec, _
         Origin:=437, StartRow:=1, DataType:=xlDelimited, _
         TextQualifier:=xlNone, ConsecutiveDelimiter:=True, _
         Tab:=True, Semicolon:=False, Comma:=False, Space:=True, _
         Other:=False, FieldInfo:=Array(Array(1, 1), _
         Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
         TrailingMinusNumbers:=True
    Set wkbRaw = ActiveWorkbook   'In case you need the reference later!
   Else
     Exit Sub  'User didn't supply a file to load
   End If
   
   [A1].Select
   lCntr = 1
   
   Do
   
      If (WorksheetFunction.IsNumber(Cells(lCntr, "A")) = False) Then
        zTemp = Format(Cells(lCntr, "C").Value)
        Cells(lCntr, "C").Formula = "=" + _
              Left(zTemp, Len(zTemp) - 3) + "0" + Right(zTemp, 3)
        
      End If
      
      lCntr = lCntr + 1
      
   Loop Until (Cells(lCntr, "A").Value = "")
   
'*** End Processing ***

   Application.DisplayAlerts = True

   With wkbRaw
       wkbRaw.SaveAs "Fixed-" & wkbRaw.Name, xlTextWindows
       .Close
   End With 'wkbRaw

End Sub  'ProcessFile

This code will prompt the user for the file with a standard “File Open” dialog.

It will then open the file and do the processing then save the file back out in the same format to the same directory with the Prefix “Fixed-” added to the file name.

You’ll note a difference in the file import specs and the method of processing the changed numbers.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

TWO slick version. Thanks RG!!

Reply | Quote

I got a new text file from the user. The layout (format) was slightly different from the original, but still a text file and still need the third field changed per the original definition (inserting a zero). I’ve attached it.

It looks like this came from a CSV file with the commas. How an I modify either macro to handle this option? Should there be a variable assigned that defines the delimiter somehow?

I was using Maud’s. Not sure if RGs handles this situation.

Reply | Quote

Kevin,

This is the code segment that needs changing:

Code:

Workbooks.OpenText Filename:=zFileSpec, _
         Origin:=437, StartRow:=1, DataType:=xlDelimited, _
         TextQualifier:=xlNone, ConsecutiveDelimiter:=True, _
         Tab:=True, Semicolon:=False, Comma:=False, Space:=True, _
         Other:=False, FieldInfo:=Array(Array(1, 1), _
         Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
         TrailingMinusNumbers:=True

You’ll notice that Comma:=False, you need to change that to true and set the other delimiters (Tab & Space) to False. You might also want to set the ConsecutiveDelimiter argument to False as with a comma delimited file two consecutive delimiters indicate a null value field.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks. The file I attached was NOT the way they’re going to send the text file after all.
SIGH.

When they resent the correctly formatted file, of course it worked.

I’ll keep your comment and code change handy just in case.

If it weren’t for customers, this would all be fun, wouldn’t it? Always an interesting challenge. Keeps the old brain working!!

Reply | Quote

RG, when I made your changes, while the final result was correct, three undesirable things happened:

1. It opened an Excel sheet with the text data in it (don’t want user to see that) and
2. It asked about saving the file…it should just do it and
3. The resulting file had double sets of quotes around the previously existing quotes in field 1 [how do I avoid this?] They have (just) confirmed that they will always have the first field with quotes around it and there will be commas separating the fields.

So, now I have to confirm if the result should have quotes (not doubled up) around the first field and commas separating the fields. If that’s the case, is that easily handled?

I suspect that’s in the IF statement where the “0” is inserted…but, there wouldn’t be a commas after the last field.

Kevin

Reply | Quote

Kevin,

Well I got it 95% of the way there! :angry:

I just can’t find an output format that will quote the first field.

Here’s my test file modified as you specified.

Code:

"69",15.4702,1041,,1,8,7052016
"69",15.4702,1041,,2,2,7052016
"595",15.4702,8110,,1,10,7052016
"594",15.4702,8110,,1,10,7052016
"524",15.4702,3260,,1,16,7052016
"524",15.4702,3260,,2,2,7052016
"str123",15.4702,3260,,2,2,7052016
"MILLER010",15.4702,13260,,2,2,7052016

You’ll notice the extra field (double commas). I used search and replace (comma for tab) which revealed the blank field that was suppressed in the first file via the ConsecutiveDelimiter:=True statement.

On import COL A values with only digits are automatically converted to NUMBERS by Excel so the testing part of the code did not need to be changed.

The viewing of the data sheet was suppressed using the Application.ScreenUpdating=False statement.

I tried every file save type I thought might work (using the list provided by the help file) and couldn’t find one that would quote the first field. Even the actual Text Values (if they had been quoted I could have written a loop to add an apostrophe in front of the COL A items that were numbers and solved the problem but no luck. I even tried changing the Miller line to Joe Miller to see if the space would solve the problem but no luck there either.

So at current the output file looks like this:

Code:

69,15.4702,1041,,1,8,7052016
69,15.4702,1041,,2,2,7052016
595,15.4702,8110,,1,10,7052016
594,15.4702,8110,,1,10,7052016
524,15.4702,3260,,1,16,7052016
524,15.4702,3260,,2,2,7052016
str123,15.4702,30260,,2,2,7052016
MILLER010,15.4702,130260,,2,2,7052016

I could maybe write some PowerShell code to fix the file but that would entail the enabling of PowerShell on the client’s machines and they may not like that.

Here’s the code to get you to this point. Note: I named it Process2 so you could just copy it into your existing test workbook and be able to compare the code and it relies on the GetFile routine already there.

Code:

Sub ProcessFile2()

   Dim zFileSpec As String
   Dim wkbMain   As Workbook
   Dim wkbRaw    As Workbook
   Dim lCntr     As Long
   Dim zTemp     As String
   
   Set wkbMain = ActiveWorkbook
   
   zFileSpec = GetFile()
   
   Application.ScreenUpdating = False
   
   If (zFileSpec  "") Then
     Workbooks.OpenText Filename:=zFileSpec, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
        Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
        TrailingMinusNumbers:=True
     Set wkbRaw = ActiveWorkbook   'In case you need the reference later!
   Else
     Exit Sub  'User didn't supply a file to load
   End If
   
   [A1].Select
   lCntr = 1
   
   Do
   
      If (WorksheetFunction.IsNumber(Cells(lCntr, "A")) = False) Then
        zTemp = Format(Cells(lCntr, "C").Value)
        Cells(lCntr, "C").Formula = "=" + _
              Left(zTemp, Len(zTemp) - 3) + "0" + Right(zTemp, 3)
        
      End If
      
      lCntr = lCntr + 1
      
   Loop Until (Cells(lCntr, "A").Value = "")
   
'*** End Processing ***

   Application.DisplayAlerts = False

   With wkbRaw
       wkbRaw.SaveAs "Fixed-" & wkbRaw.Name, xlCSV
       .Close
   End With 'wkbRaw

End Sub  'ProcessFile2

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Attached is my revised code that, instead of importing the entire text file into Excel then exporting it modified to a new file, will import text line-by-line, modify it if needed, then export it to a revised text file.

The output places the first field with quotes as the original but zeroes added to the 3rd field if needed and comma delimited.

OutPut file with modified 3rd field:
45208-KW2

In a Standard Module:

Code:

Public Sub Controller()
Application.ScreenUpdating = False
[COLOR=”#008000″]’——————————————
‘DECLARE VARIABLES[/COLOR]
Dim Filter As String
Dim Caption As String
Dim Fname As String
Dim CurPath As String
[COLOR=”#008000″]’——————————————
‘SET VARIABLES[/COLOR]
Filter = “Text files (*.txt),*.txt”
Caption = “Please Select the source file ”
CurPath = CurDir     [COLOR=”#008000″]’GET CURRENT DIRECTORY[/COLOR]
ChDir (“C:Users”)
Fname = Application.GetOpenFilename(Filter, , Caption)
FilePath = Left(Fname, Len(Fname) – 4) & “_Revised.txt”
[COLOR=”#008000″]’——————————————
‘CREATE TEXT FILE[/COLOR]
Open FilePath For Output As #2
GetText (Fname)
Close #1
Close #2
MsgBox “New text file has been created:” & Chr(32) & FilePath
End Sub



Public Sub GetText(file)
Sep = “,” [COLOR=”#008000″]’DEFINES SEPERATOR USED[/COLOR]
[COLOR=”#008000″]’——————————————-
‘RETRIEVE LINE BY LINE[/COLOR]
Open file For Input Access Read As #1
While Not EOF(1)
    Line Input #1, Wholeline
[COLOR=”#008000″]’——————————————-
‘MODIFY LINE[/COLOR]
    s = Split(Wholeline, “,”)
    s(0) = Mid(s(0), 2, Len(s(0)) – 2)
    If Not IsNumeric(s(0)) Then
        Num1 = Left(s(2), Len(s(2)) – 3)
        Num2 = Right(s(2), 3)
        s(2) = Num1 & “0” & Num2
    End If
    s(0) = Chr(34) & s(0) & Chr(34)
    Wholeline = “”
    For I = 0 To UBound(s)
        If Wholeline = “” Then
            Wholeline = s(0)
        Else:
            Wholeline = Wholeline & Sep & s(I)
        End If
    Next I
[COLOR=”#008000″]’——————————————-
‘WRITE MODFIED LINE TO NEW TEXT FILE[/COLOR]
    WriteText Left(file, Len(file) – 4), Wholeline
Wend
End Sub


Public Sub WriteText(FilePath, LineText)
FilePath = FilePath & “_Revised.txt”
Print #2, LineText
End Sub

Reply | Quote

Brilliant!

Learning question, Maud:

This line: s = Split(Wholeline, “,”)

Could is be: s = Split(Wholeline, Sep)

In the event of a change sometime? What would Sep become if the separator was a TAB?

Reply | Quote

Good pickup kw! I created the Sep variable to use when outputting the data line but forgot to change it when inputting. For Tab delimited, Sep=Chr(9) which is the ASCII code for horizontal tab might be the way to go.

Maud

Reply | Quote

If there will be times when the delimiters for the input and/or the output text files will change, this version 4 will add versatility. After the user browses to and selects the correct input text file, the input and output delimiters can be selected from a popup form.

45222-kw4

Added: Version 5 will automatically detect the delimiter of the input text file and still allow selection for the output delimiter

Reply | Quote

Thanks for that, but I just found an issue. I’m testing this one:

“0069”,15.4702,1041,,1,8,7052016
“0069”,15.4702,1041,,2,2,7052016
“0595”,15.4702,8110,,1,10,7052016
“0594”,15.4702,8110,,1,10,7052016
“0524”,15.4702,3260,,1,16,7052016
“0524”,15.4702,3260,,2,2,7052016
“Justin1234”,15.4702,13260,,1,16,7052016
“jwm524”,15.14702,3260,,2,2,7052016

And, the additional “0” should ONLY be on the last two rows because those have alpha chars inside the quotes.
The others are really numbers, so the 3rd field is left alone.

I’m wondering if the ” in the original are causing the 0069 (etc) NOT to be a number?!?!?

Reply | Quote

KW,

I ran the file JWTEST.txt using the FixTxt_Revision5 file and outputted the data as comma delimited text. Below are my results.

Original
45235-JWTEST

Revised
45236-JWTEST_Revised

Only the third value of the last two lines were modified as you would expect. Were you getting something different?

Reply | Quote

I was using the original controller macro and all of the lines had an extra 0.

Your v5 worked for me also, but I don’t want to prompt the user for the delimiter. I want to lock it in.
I can’t find out where in your macro you have that so I can comment it out.

Reply | Quote

_Revised3 is the version that will do what you want

Reply | Quote

Maud, using Revided3 I almost have my other (QuickBooks-related) issue solved. Maybe you or someone could resolve the remaining two issues. Also, I do NOT need to convert the file type to “iif” but can keep it as a text file.

The original “iif” as a text file is attached as is the original file where Vlookup should be done with the text file.
Sheet1 is what I get, but Sheet2 is what would happen if the macro does its thing.

Reply | Quote

KW,

Here is the amended code to write to modify and save your new text file to a revised version to include the P Item.

Note: John Smith in your text file is listed as “Smith, John ” with a trailing space. Added to John Smith in your workbook is a trailing space so his name would match (Smith, John). Attached is your original text file and the revised TimeTemplate.

Maud

Code:

Public Sub Controller()
Application.ScreenUpdating = False
[COLOR=”#006400″]’——————————————
‘DECLARE VARIABLES[/COLOR]
Dim Filter As String
Dim Caption As String
Dim Fname As String
Dim CurPath As String
[COLOR=”#006400″]’——————————————
‘SET VARIABLES[/COLOR]
Filter = “Text files (*.txt),*.txt”
Caption = “Please Select the source file ”
CurPath = CurDir     ‘GET CURRENT DIRECTORY
ChDir (“C:Users”)
Fname = Application.GetOpenFilename(Filter, , Caption)
FilePath = Left(Fname, Len(Fname) – 4) & “_RevisedQB.txt”
[COLOR=”#006400″]’——————————————
‘CREATE TEXT FILE[/COLOR]
Open FilePath For Output As #2
GetText (Fname)
Close #1
Close #2
MsgBox “New text file has been created:” & Chr(32) & FilePath

End Sub


Public Sub GetText(file)
Dim I As Integer, J As Integer
Sep = Chr(9) [COLOR=”#006400″]’DEFINES SEPERATOR USED such as “,” or Chr(9) for tab character[/COLOR]
[COLOR=”#006400″]’——————————————-
‘RETRIEVE LINE BY LINE[/COLOR]
Open file For Input Access Read As #1
For I = 1 To 3:: Line Input #1, Wholeline:: Next I
While Not EOF(1)
    Line Input #1, Wholeline
[COLOR=”#006400″]’——————————————-
‘MODIFY LINE[/COLOR]
    s = Split(Wholeline, Sep)
    emp = Mid(s(3), 2, Len(s(3)) – 2)
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 11 To LastRow
        If Cells(I, 1) = emp Then
            s(9) = Cells(I, 2)
            Wholeline = “”
            For J = 0 To UBound(s)
                If Wholeline = “” Then
                    Wholeline = s(0)
                Else:
                    Wholeline = Wholeline & Sep & s(J)
                End If
            Next J
            GoTo nextemp
        End If
    Next I
[COLOR=”#006400″]’——————————————-
‘WRITE MODFIED LINE TO NEW TEXT FILE[/COLOR]
nextemp:
    WriteText Left(file, Len(file) – 4), Wholeline
Wend
End Sub

Public Sub WriteText(FilePath, LineText)
FilePath = FilePath & “_RevisedQB.txt”
Print #2, LineText
End Sub

Reply | Quote

Maud, spectacular as usual. You’re up on the Rio podium for a GOLD.

I caught the add’l space after I sent the file. Good catch!

I’m learning, I’m learning. I got fairly close myself with this and am excited! A good learning process for this aging mind.

Thanks, again.

Kevin

Reply | Quote

Thanks kw. Us ole timers (you, me, RG and anyone else who is consistently constipated), still have a few tricks in our bags.

Reply | Quote

Maud, two questions:

1. Is there a way to look at ALL FILES rather than just TEXT files? What would I change?

2. If someone cancels when looking for a file, the macro bombs. Can I add something to just bail at that point?

Reply | Quote

Kevin,

Change these lines:

Code:

[COLOR="#0000FF"]Filter = "ALL files (*.*),*.*"[/COLOR]
Caption = "Please Select the source file "
CurPath = CurDir     'GET CURRENT DIRECTORY
ChDir ("C:Users")
Fname = Application.GetOpenFilename(Filter, , Caption)
[COLOR="#0000FF"]If (Fname = "False") Then Exit Sub[/COLOR]

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks, RG, for those two additional lines.

Now, suppose with the user navigating and seeing all files then inadvertently (polite) selected something that Excel doesn’t recognize. If so, the macro crashes again. Can that line: If (Fname = “False”) Then Exit Sub be used elsewhere to resolve this?
If they try, as I suspect they might, to open an “iif” file from QuickBooks, the macro crashes. If I were in Excel and tried to do that directly, Excel would want to know about delimiters, etc.

Reply | Quote

Thanks, RG, for those two additional lines.

Now, suppose with the user navigating and seeing all files then inadvertently (polite) selected something that Excel doesn’t recognize. If so, the macro crashes again. Can that line: If (Fname = “False”) Then Exit Sub be used elsewhere to resolve this?
If they try, as I suspect they might, to open an “iif” file from QuickBooks, the macro crashes. If I were in Excel and tried to do that directly, Excel would want to know about delimiters, etc.

Reply | Quote

Kevin,

The answer is to setup the filter so it only allows acceptable file types. In other words, don’t give the user the capability to select an incompatible file!.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG,

Thanks for adding the code to enable cancelling the file selection

KW,

The code was written to look at text files only and will surely fail if you select any thing other than a text file whether Excel can open the file type or not. There would need to be some conditional test to evaluate the file type then run the appropriate code.

Reply | Quote

Here’s my dilemma. They want to open text files, which open just fine w/this macro.
They also want/need to open QuickBooks “iif” files which are glorified text files. In the case of QB, the “real” data that we’re dealing with in this macro begins in row 4 (3 is column or field headers and 1 and 2 are misc. junk that QB uses).

If I do this: Filter = “Text files (*.iif),*.iif” I only get to select the IIF files.

I saw this in some other code and it seemed to show both txt ad iif files.

.Filters.Add “Text Files”, “*.iif”

Is there a way to handle both?

Reply | Quote

Kevin,

Try this:
[noparse]Filter = “Convertable Files(*.Txt;*.CSV;*.*iif),*.Txt;*.CSV;*.iif”[/noparse]

45325-FilterDialogOpen

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

That was interesting!! [ there’s SOOO much to learn ]
The remaining issue is if they pick the IIF file, the data doesn’t start until row 4.

The macro removes the first three rows which I believe I need to retain it they pick an iif file so they can put it back into QuickBooks.

I just noticed that an iif converted to text will also retain those top three rows.

So, the macro needs to retain the 1st 3 and then apply the look-ups to the remaining.

Reply | Quote

Kevin,

Here’s some code that should work {untested}!

Change this:

Code:

'-------------------------------------------
'RETRIEVE LINE BY LINE
Open file For Input Access Read As #1
For I = 1 To 3:: Line Input #1, Wholeline:: Next I
While Not EOF(1)
    Line Input #1, Wholeline
'-------------------------------------------

To this:

Code:

'-------------------------------------------
'RETRIEVE LINE BY LINE
Open file For Input Access Read As #1

Ext = Split(Fname,".")    'Dim this as a Variant!
If (UCase(Ext(1)) = "IIF" Then
  While Not EOF(1)
       For I = 1 To 3
          Line Input #1, Wholeline
	      WriteText Left(file, Len(file) - 4), Wholeline
       Next I
   WEnd
End If '.IIF file

While Not EOF(1)
    Line Input #1, Wholeline
'-------------------------------------------

Now you didn’t ask or specify what to do with the .txt files. The solution above will process ALL lines in a .txt file.

You’ll probablly want to modify the lines w/ “_RevisedQB.txt” to test and select .txt or .iif?

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

In ALL cases, the first 3 lines are not checked and are repeated in the resulting file.

When I modified Maud’s macro according to what you just wrote, this line: If (UCase(Ext(1)) = “IIF” Then that line turned RED. AH I see it…extra paren.

PS. the final result will always be a text file. They can manually change the extension.

OK…I get an error here: If UCase(Ext(1)) = “IIF” Then

I did a DIM as variant at the top, so that shouldn’t be the issue.

Code:

Public Sub GetText(file)
Dim I As Integer, J As Integer
Dim Ext As Variant

Reply | Quote

OOPS!

Missing ) after “IIF”…. If (UCase(Ext(1)) = “IIF”) Then

My Bad!

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Kevin,

Did you Dim Ext as Variant?

You should just copy the code and paste it between the lines…that’s why I included the lines.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I did make it a variant and copied the code exactly with the added paren:

Code:

Open file For Input Access Read As #1

Ext = Split(Fname, ".")   'Dim this as a Variant!
If (UCase(Ext(1)) = "IIF") Then
  While Not EOF(1)
       For I = 1 To 3
          Line Input #1, Wholeline
          WriteText Left(file, Len(file) - 4), Wholeline
       Next I
   Wend
End If '.IIF file

While Not EOF(1)
    Line Input #1, Wholeline

Still get the error on the IF line: If (UCase(Ext(1)) = “IIF”) Then

The error is “script out of range”

Reply | Quote

Kevin,

Code:

Public Sub GetText([COLOR="#0000FF"]file[/COLOR])
Dim I As Integer, J As Integer
Dim Ext As Variant
Sep = Chr(9) 'DEFINES SEPERATOR USED such as "," or Chr(9) for tab character
'-------------------------------------------
'RETRIEVE LINE BY LINE
'Open file For Input Access Read As #1
'For I = 1 To 3:: Line Input #1, Wholeline:: Next I
'While Not EOF(1)
'    Line Input #1, Wholeline

'RETRIEVE LINE BY LINE

Open file For Input Access Read As #1

Ext = Split[COLOR="#0000FF"](Fname[/COLOR], ".")   'Dim this as a Variant!
If (UCase(Ext(1)) = "IIF") Then
  While Not EOF(1)
       For I = 1 To 3
          Line Input #1, Wholeline
          WriteText Left(file, Len(file) - 4), Wholeline
       Next I
   Wend
End If '.IIF file

While Not EOF(1)
    Line Input #1, Wholeline

The parts in blue have to match! My bad looking at to many partial pieces of code and not the whole picture. This is why it’s hard to debug this way. If I could step through the code it would be a lot easier, GRRRRR!

BTW: Just to be clear change Fname to File in the EXT = line.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Well, that was the key to getting through it. However, none of the fields were changed (sigh).
The first 3 rows were put into the resulting text file, but the other rows were not adjusted now. groan.

after the “retrieve line by line…” this is what should have done the modifications:

Code:

'MODIFY LINE
    s = Split(Wholeline, Sep)
    emp = Mid(s(3), 2, Len(s(3)) - 2)  'use when employee name has quotes around it
  ' emp = s(3) ' use when employee name does not have quotes around it
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 11 To LastRow
        If Cells(I, 1) = emp Then
            s(9) = Cells(I, 2)
            Wholeline = ""
            For J = 0 To UBound(s)
                If Wholeline = "" Then
                    Wholeline = s(0)
                Else:
                    Wholeline = Wholeline & Sep & s(J)
                End If
            Next J
            GoTo nextemp
        End If
    Next I
'-------------------------------------------
'WRITE MODFIED LINE TO NEW TEXT FILE
nextemp:
    WriteText Left(file, Len(file) - 4), Wholeline
Wend
End Sub

Public Sub WriteText(FilePath, LineText)
FilePath = FilePath & "_RevisedQB.txt"
Print #2, LineText
End Sub

Reply | Quote

Hi KW

..why not have two buttons instead of one??

see attached

..I haven’t checked this out fully yet – just a start on what Maud and RG have already done very nicely.
(but I still wouldn’t dim I as an integer when using it as For I = 11 To LastRow with rows available up to 1,048,576)

zeddy

Reply | Quote

Kevin,

I wrote this little test program and I don’t get an error on that line!

Code:

Sub Test()
Fname = "G:BEKDOCSMovDel.Log"
Ext = Split(Fname, ".")   'Dim this as a Variant!
If (UCase(Ext(1)) = "IIF") Then
  Debug.Print "IIF FIle"
Else
  Debug.Print Ext(1) & " File"

End If '.IIF file
End Sub

Note: I got rid of the Option Explicit so I didn’t have to DIM the variables.
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Here’s the full body of the code where the error happens:

Code:

Public Sub GetText(file)
Dim I As Integer, J As Integer
Dim Ext As Variant
Sep = Chr(9) 'DEFINES SEPERATOR USED such as "," or Chr(9) for tab character
'-------------------------------------------
'RETRIEVE LINE BY LINE
'Open file For Input Access Read As #1
'For I = 1 To 3:: Line Input #1, Wholeline:: Next I
'While Not EOF(1)
'    Line Input #1, Wholeline

'RETRIEVE LINE BY LINE

Open file For Input Access Read As #1

Ext = Split(Fname, ".")   'Dim this as a Variant!
If (UCase(Ext(1)) = "IIF") Then
  While Not EOF(1)
       For I = 1 To 3
          Line Input #1, Wholeline
          WriteText Left(file, Len(file) - 4), Wholeline
       Next I
   Wend
End If '.IIF file

While Not EOF(1)
    Line Input #1, Wholeline

I just commented out the “old stuff” just in case.

Reply | Quote

Not sure if I can convince user that two buttons would be the right approach.

Your macro, however, broke here: If Fname = False Then Exit Sub with a type mismatch error.

P.S. Just got an email from the user saying that ONLY “iif” files would be opened and converted.

Reply | Quote

Hi KW

The line..
If Fname = False Then Exit Sub
..worked OK on my system (even with Dim Fname as String).
I added this line to allow for the case when the User clicks the [Cancel] button on the file-selection-dialog (i.e. rather than picking a file).
What happens in your version if the User clicks that [Cancel] button???

You could try..
If Fname = “False” Then Exit Sub

zeddy

Reply | Quote

This line works to exit the user from the macro if they click CANCEL: If (Fname = “False”) Then Exit Sub

I guess it’s fussy about the parens.

Reply | Quote