VBA Code modification to print a range to a .txt file

[rollnation2]

Hello,

Reposting an unsolved dilemma.

I am trying to modify a macro which exports a dynamic range to a text file, the range B2:O* where * is the last used cell in column O.

The code below works well but....

1. It prints all cells whereas I want to print only Columns B:O
2. I want the VBA to name the txt file what is in cell T2 of the sameworksheet.

Worksheet name = "KTR ERP UPLOAD FY22"

*********************************************************

Sub CrText()


Dim c00 As Variant
Dim textFilePath As String
Dim lngCounter As Long
Dim FF As Integer


textFilePath = CStr(VBA.CurDir) & "\mTextFile.txt"


FF = VBA.FreeFile
c00 = Range("A1").CurrentRegion


Open textFilePath For Output As #FF
For lngCounter = LBound(c00, 1) To UBound(c00, 1)
Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
Next
Close #FF
Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus
End Sub
[CODE=vba]

[/CODE]

 

[Domenic]

To print only Columns B through O, first find the last used row using one of your columns, let's say Column B . . .

    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row

Then assign the desired range to your variable as follows . . .

    c00 = Range("B1:O" & lastRow).Value

Then, to name your text file using the value in cell T2, try . . .

    textFilePath = CStr(VBA.CurDir) & "\" & Range("T2").Value & ".txt"

Here's your code amended as above . . .

Sub CrText()

    Dim c00 As Variant
    Dim textFilePath As String
    Dim lngCounter As Long
    Dim FF As Integer
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    c00 = Range("B1:O" & lastRow).Value
        
    textFilePath = CStr(VBA.CurDir) & "\" & Range("T2").Value & ".txt"
    
    FF = VBA.FreeFile
    
    Open textFilePath For Output As #FF
        For lngCounter = LBound(c00, 1) To UBound(c00, 1)
            Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
        Next
    Close #FF
    
    Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus
    
End Sub

Hope this helps!

 

[rollnation2]

To print only Columns B through O, first find the last used row using one of your columns, let's say Column B . . .

    Dim lastRow As Long
   
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row

Then assign the desired range to your variable as follows . . .

    c00 = Range("B1:O" & lastRow).Value

Then, to name your text file using the value in cell T2, try . . .

    textFilePath = CStr(VBA.CurDir) & "\" & Range("T2").Value & ".txt"

Here's your code amended as above . . .

Sub CrText()

    Dim c00 As Variant
    Dim textFilePath As String
    Dim lngCounter As Long
    Dim FF As Integer
    Dim lastRow As Long
   
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
   
    c00 = Range("B1:O" & lastRow).Value
       
    textFilePath = CStr(VBA.CurDir) & "\" & Range("T2").Value & ".txt"
   
    FF = VBA.FreeFile
   
    Open textFilePath For Output As #FF
        For lngCounter = LBound(c00, 1) To UBound(c00, 1)
            Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
        Next
    Close #FF
   
    Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus
   
End Sub

Hope this helps!

Thanks Domenic!

The code modifications work perfectly!
Much appreciated!

 

[Domenic]

That's great, I'm glad I could help.

Cheers!

 

[rollnation2]

That's great, I'm glad I could help.

Cheers!

Hi Domenic,

One last question...

How do I export dates in custom format?

• Column B is all dates formatted yyyymmdd

My workbook structures the dates in this custom format. However, a date in my worksheet would be formatted as 20220215, but that exports in date format "2/15/2022" in notepad.

Is there a modification to the VBA I can make to have it export those dates in column B in yyyymmdd format?

Thanks again!
Aaron

 

[Domenic]

Before printing to your file, assign the row to a temporary array variable, format the date in that variable, and then print to your file . . .

Sub CrText()

    Dim c00 As Variant
    Dim temp As Variant
    Dim textFilePath As String
    Dim lngCounter As Long
    Dim FF As Integer
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    c00 = Range("B1:O" & lastRow).Value
        
    textFilePath = CStr(VBA.CurDir) & "\" & Range("T2").Value & ".txt"
    
    FF = VBA.FreeFile
    
    Open textFilePath For Output As #FF
        For lngCounter = LBound(c00, 1) To UBound(c00, 1)
            temp = Application.Index(c00, lngCounter, 0)
            temp(1) = Format(temp(1), "yyyymmdd")
            Print #FF, Join(temp, vbTab)
        Next
    Close #FF
    
    Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus
    
End Sub

Hope this helps!

 

[rollnation2]

Before printing to your file, assign the row to a temporary array variable, format the date in that variable, and then print to your file . . .

Sub CrText()

Dim c00 As Variant
Dim temp As Variant
Dim textFilePath As String
Dim lngCounter As Long
Dim FF As Integer
Dim lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

c00 = Range("A2:O" & lastRow).Value

textFilePath = CStr(VBA.CurDir) & "\" & Range("T2").Value & ".txt"

FF = VBA.FreeFile

Open textFilePath For Output As #FF
For lngCounter = LBound(c00, 1) To UBound(c00, 1)
temp = Application.Index(c00, lngCounter, 0)
temp(1) = Format(temp(1), "yyyymmdd") ----> Error 6 occurs here
Print #FF, Join(temp, vbTab)
Next
Close #FF

Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus

End Sub

It worked perfectly again. Thanks!
Trying to modify it for another WB with the range shifted to A2:N, but am getting a error 6 : Overflow.

Probably a super easy fix but none of the code modifications I am making are working.
How do I modify for a range of A2:N where the date formatting would occur in column A?

My poor attempt below is not working? ahhhh! :/

Sub CrText()

Dim c00 As Variant
Dim temp As Variant
Dim textFilePath As String
Dim lngCounter As Long
Dim FF As Integer
Dim lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

c00 = Range("A2:N" & lastRow).Value

textFilePath = CStr(VBA.CurDir) & "\" & Range("T2").Value & ".txt"

FF = VBA.FreeFile

Open textFilePath For Output As #FF
For lngCounter = LBound(c00, 1) To UBound(c00, 1)
temp = Application.Index(c00, lngCounter, 0)
temp(1) = Format(temp(1), "yyyymmdd")
Print #FF, Join(temp, vbTab)
Next
Close #FF

Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus

End Sub

 

[Domenic]

When the error occurs and you click on Debug, which line gets highlighted?

 

[rollnation2]

You didn't say which line is causing the error, but it looks like you'll need to declare FF as Long, not Integer . . .

Dim FF As Long

Does this help?

Hmm. Declaring FF as Long didnt work.

This line is causing the error 6:
" temp(1) = Format(temp(1), "yyyymmdd")"

 

[Domenic]

When the error occurs, what value is assigned to temp(1) ? You can check by entering the following line in the Immediate Window ( Ctrl + G ) . . .

? temp(1)