M
MikeT
Hi!
I have to save a worksheet in tab-delimited text format, and I cannot retain
the quotation mark text delimiters that are added to cell values that contain
commas. After searching around for ideas on how to eliminate the quotation
marks, I found a technique whereby I essentially print to a file directly,
rather than simply saving the sheet in tab-delimited text format. But my
solution runs extremely slowly, taking about 20 minutes to save a sheet 150
columns by 2500 rows. Here's my code - can anyone tell me how to make it run
faster, like about 20 times faster?
Dim TextLine As String
Dim i, j, FileNo As Integer
Dim OutFileName, TextLine As String
Dim sh As Worksheet
Dim fd As FileDialog
Dim f, fs, s, ts As Variant
Dim wb As Workbook
'Prompt the user for a folder to put the text files
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.InitialFileName = "C:\Temp\"
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
For Each sh In ActiveWorkbook.Worksheets
If Mid(sh.Name, 1, 18) = "MatMasterLoadSheet" Then
FileNo = FreeFile
OutFileName = fd.InitialFileName & sh.Name & ".txt"
Open OutFileName For Output Access Write As #FileNo
For i = 1 To sh.UsedRange.Rows.Count
'Reset the text line at new row
TextLine = ""
For j = 1 To sh.UsedRange.Columns.Count
If j = sh.UsedRange.Columns.Count Then
'Add the last cell to the textline, no tab, and print the line
TextLine = TextLine & sh.Cells(i, j)
Print #FileNo, TextLine
Else
'Append column content to textline, with a tab
TextLine = TextLine & sh.Cells(i, j) & vbTab
End If
Next j
Next i
Close #FileNo
End If
Next sh
Else
MsgBox "Worksheets NOT Saved!!" & vbCrLf & "Please drive through!!!"
End If
End With
I have to save a worksheet in tab-delimited text format, and I cannot retain
the quotation mark text delimiters that are added to cell values that contain
commas. After searching around for ideas on how to eliminate the quotation
marks, I found a technique whereby I essentially print to a file directly,
rather than simply saving the sheet in tab-delimited text format. But my
solution runs extremely slowly, taking about 20 minutes to save a sheet 150
columns by 2500 rows. Here's my code - can anyone tell me how to make it run
faster, like about 20 times faster?
Dim TextLine As String
Dim i, j, FileNo As Integer
Dim OutFileName, TextLine As String
Dim sh As Worksheet
Dim fd As FileDialog
Dim f, fs, s, ts As Variant
Dim wb As Workbook
'Prompt the user for a folder to put the text files
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.InitialFileName = "C:\Temp\"
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
For Each sh In ActiveWorkbook.Worksheets
If Mid(sh.Name, 1, 18) = "MatMasterLoadSheet" Then
FileNo = FreeFile
OutFileName = fd.InitialFileName & sh.Name & ".txt"
Open OutFileName For Output Access Write As #FileNo
For i = 1 To sh.UsedRange.Rows.Count
'Reset the text line at new row
TextLine = ""
For j = 1 To sh.UsedRange.Columns.Count
If j = sh.UsedRange.Columns.Count Then
'Add the last cell to the textline, no tab, and print the line
TextLine = TextLine & sh.Cells(i, j)
Print #FileNo, TextLine
Else
'Append column content to textline, with a tab
TextLine = TextLine & sh.Cells(i, j) & vbTab
End If
Next j
Next i
Close #FileNo
End If
Next sh
Else
MsgBox "Worksheets NOT Saved!!" & vbCrLf & "Please drive through!!!"
End If
End With