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]

 

[rollnation2]

When I hover over the error it correctly shows the date format.

• temp(1)=20220218

 

[Domenic]

If when the error occurs and you click Debug that line is highlighted, then I don't know why you would be getting an overflow error on that line. So hold on for now, and maybe someone else here might know why.

 

[rollnation2]

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)

Not sure if I am using the immediate window correctly. Here is a screenshot.

 

[rollnation2]

Not sure if I am using the immediate window correctly. Here is a screenshot.

Here is an image of the actual worksheet. PII columns blacked out.

 

[Domenic]

Oh I see. In this set of data, you have the date in a different format. Since dates are stored as serial numbers, the Format function tries to convert that number into a date. However, since a date cannot exceed December 31, 9999, it causes the overflow error.

So since that date is already in the desired format, you can simply delete that line of code. Or, you can amend the code so that it first checks to make sure that the value in the first column is in fact a true date before format it, like this . . .

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

 

[rollnation2]

Oh I see. In this set of data, you have the date in a different format. Since dates are stored as serial numbers, the Format function tries to convert that number into a date. However, since a date cannot exceed December 31, 9999, it causes the overflow error.

So since that date is already in the desired format, you can simply delete that line of code. Or, you can amend the code so that it first checks to make sure that the value in the first column is in fact a true date before format it, like this . . .

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

YOU ARE THE MAN.
Thanks!

 

[Domenic]

You're very welcome, cheers!