I was sent an excel file with the following macro (see end of message) which is supposed to export data from the spreadsheet
into a pipes delimited text file, for use by another application. however, I cannot get the macro to work. The only
immediate issue is that the macro was scripted in another environment using Excel 2000 and we are using Excel 2002. I did
not think that would matter much, but here I am. I am not very good with Excel macros yet and I frankly do not see what is
wrong with this macro.
All assistance greatly appreciated.
strFilename As String
Dim strPipe As String
Dim objRange As Excel.Range 'Range of selected fields.
Dim dblFile As Double 'Output file handle.
Dim lngRow As Double 'Loop counter for loop through rows.
Dim lngColumn As Double 'Loop counter for loop through columns.
Dim strBuffer As String 'Buffer containing output line when being compiled.
'Handle ANY error by
On Error GoTo Err_Export_Failed
'Get user options.
strFilename = InputBox("Enter full path & filename to export to.", "Export Util", "C:Ouput.txt")
strPipe = InputBox("Enter pipe deliminator character.", "Export Util", "|")
'Get Currently selected range
Set objRange = Application.Selection
'Open output file.
dblFile = FreeFile 'Grab free file handle.
Open strFilename For Output As dblFile
For lngRow = LBound(objRange.Value, 1) To UBound(objRange.Value, 1)
strBuffer = ""
For lngColumn = LBound(objRange.Value, 2) To UBound(objRange.Value, 2)
'Add Pipe delimantor if not first cell.
If (lngColumn > LBound(objRange.Value, 2)) Then
strBuffer = strBuffer & strPipe
'Append next cell to output buffer.
strBuffer = strBuffer & Trim(objRange.Values(lngRow, lngColumn))
Print #dblFile, strBuffer
MsgBox "Selected fields exported to " & strFilename, vbInformation, "Export Util"
MsgBox "Failed to export to " & strFilename, vbInformation, "Export Util"