Completely new to VBA, and here for that matter. I'm trying to write a script for converting data in an Excel spreadsheet to a flat file (.txt) for loading into an ERP.
I've got as far as piecing together the below script, but have two problems that I'm hoping someone can point me in the right direction for resolving.
- The flat file has blank rows at the bottom of the data.
- The script can't seem to handle large volumes. I'm getting overflow errors at around the 30,000 row mark.
Here's what I've got so far, apologies if it's messy:
Private Sub CommandButton2_Click()
' Turns all the numbers and stuff into a flat file!!!
' Defines things.
Dim myPRFile As String
Dim TextFile As Integer
Dim FileContent As String
Dim rng As Range
Dim cellValue As Variant
Dim i As Integer
Dim j As Integer
' Defines the file to use and the part of the spreadsheet to copy from.
myPRFile = "C:\Users\my.folder\Documents\Docs\Logics\flat.txt"
Range("B2").CurrentRegion.Select
Set rng = Selection
' Loops through the data puts it in the text file.
Open myPRFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then
Print #1, cellValue
Else
Print #1, cellValue; "|";
End If
Next j
Next i
Close #1
' Takes the spaces out of the file.
TextFile = FreeFile
Open myPRFile For Input As TextFile
FileContent = Input(LOF(TextFile), TextFile)
Close TextFile
FileContent = Replace(FileContent, " ", "")
Open myPRFile For Output As TextFile
Print #TextFile, FileContent
Close TextFile
' Opens the finished product.
Dim fso As Object
Dim sfile As String
Set fso = CreateObject("shell.application")
sfile = "C:\Users\my.folder\Documents\Docs\Logics\flat.txt"
fso.Open (sfile)
End Sub
It's perfectly useful as is, but I'd love to crack these last couple of things if possible.
