Exporting from Excel via VBA - too much data?

[crazy8]

As you can see from my code (below) I am formatting data from a spreadsheet and putting the results into a .txt file - that will later be FTP'd to the mainframe.

There are 171,148 rows.


If only the first 30,000 rows are run (by putting 30,000 in the range (O2) it works fine. It will not, however, handle larger amounts (overflow).


Can anyone suggest a good work-around?

-------------------------------------

Here is the VBA code:

' Put this in O1: 2 (first row of data)

' Put this in O2: 171148 (last row of data)

Sub MakeFixedWidth()

Dim MyStr As String, PageName As String, FirstRow As Integer, LastRow As Integer, MyRow As Integer

PageName = "C:Documents and Settingsc-pskwiotMy DocumentsSpreadsheetsLIPS LienRequest" & Format(Time, "HHMM") & ".txt" ' location and name of saved file

FirstRow = Range("o1").Value ' the range of the table to be exported

LastRow = FirstRow + Range("O2").Value - 1

Open PageName For Output As #1

For MyRow = FirstRow To LastRow ' loop through each row of the table

MyStr = ""

MyStr = Cells(MyRow, 1).Value & String(75 - Len(Cells(MyRow, 1).Value), " ") ' Taxpayer-Name

MyStr = MyStr & Cells(MyRow, 2).Value & String(10 - Len(Cells(MyRow, 2).Value), " ") ' Federal-ID

MyStr = MyStr & Format(Cells(MyRow, 3).Value, "0000000") ' Box

MyStr = MyStr & Cells(MyRow, 4).Value ' Penalty Code

MyStr = MyStr & Format(Cells(MyRow, 5).Value, "00") ' Tax-Type

MyStr = MyStr & Cells(MyRow, 6).Value & String(40 - Len(Cells(MyRow, 6).Value), " ") ' Tax-Type-Description

MyStr = MyStr & Cells(MyRow, 7).Value & String(1 - Len(Cells(MyRow, 7).Value), " ") ' Tax-Code-Prefix

MyStr = MyStr & Cells(MyRow, 8).Value & String(2 - Len(Cells(MyRow, 8).Value), " ") ' Tax-Code-Body

MyStr = MyStr & Cells(MyRow, 9).Value & String(26 - Len(Cells(MyRow, 9).Value), " ") ' Settlement-Date

MyStr = MyStr & Format(Cells(MyRow, 10).Value, "00") ' Tax-Year

MyStr = MyStr & Cells(MyRow, 11).Value & String(26 - Len(Cells(MyRow, 11).Value), " ") ' Tax-Period-Date

MyStr = MyStr & Format(Cells(MyRow, 12).Value, "0000000000000;-000000000000;0000000000000") ' Amount

MyStr = MyStr & Cells(MyRow, 13).Value & String(10 - Len(Cells(MyRow, 13).Value), " ") ' Status

MyStr = MyStr & Cells(MyRow, 14).Value & String(31 - Len(Cells(MyRow, 14).Value), " ") ' Status

Print #1, MyStr

Next

Close #1

Sheets("Sheet1").Range("O3").ClearContents ' note that this row expects the worksheet to be named Sheet1

Sheets("Sheet1").Hyperlinks.Add Range("O3"), PageName

End Sub

 

[Luke M]

An Integer type variable is limited in value from -32,768 to 32,767. When you tried to assign 171148, you get an overflow. I'd recommend changing your variable declarations to Long, as Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.

[pre]

Dim MyStr As String, PageName As String, FirstRow As Long, LastRow As Long, MyRow As Long

[/pre]
Reference: VB help file

 

[crazy8]

YES!!!

Many thanks - and warm regards.


Paul

 

[Luke M]

You're very welcome, glad I could help. =)