I got an access vba function works well when converting regular tab delimited text file to excel file. However, when there is too many columns (178 columns)(wrapped at 114th column), the function errors out with the following error message.
I will be very grateful if you can help me to resolve this problem. Thank you !
Run-time error '1004'; Application-defined or object-defined error.
highlight at this line in the following function
.ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
Here is the function:
Public Function ProcessTextFile(ByVal TextFileName As String, ByVal delim1 As String, ByVal ExcelFileName As String)
Dim appExcel As Excel.Application
Dim intHandle As Integer
Dim strLine As String
Dim varElements As Variant
Dim intCol As Integer
Dim intRow As Integer
Set appExcel = New Excel.Application
With appExcel
'.Visible = True
.Visible = False
.Workbooks.Add
intHandle = FreeFile
Open TextFileName For Input As #intHandle
Do Until EOF(intHandle)
intRow = intRow + 1
Line Input #intHandle, strLine
varElements = Split(strLine, delim1)
For intCol = 0 To UBound(varElements)
.ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
.Selection = varElements(intCol)
Next intCol
Loop
Close #intHandle
.ActiveWorkbook.SaveAs filename:=ExcelFileName
.Quit
End With
Set appExcel = Nothing
End Function
I will be very grateful if you can help me to resolve this problem. Thank you !
Run-time error '1004'; Application-defined or object-defined error.
highlight at this line in the following function
.ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
Here is the function:
Public Function ProcessTextFile(ByVal TextFileName As String, ByVal delim1 As String, ByVal ExcelFileName As String)
Dim appExcel As Excel.Application
Dim intHandle As Integer
Dim strLine As String
Dim varElements As Variant
Dim intCol As Integer
Dim intRow As Integer
Set appExcel = New Excel.Application
With appExcel
'.Visible = True
.Visible = False
.Workbooks.Add
intHandle = FreeFile
Open TextFileName For Input As #intHandle
Do Until EOF(intHandle)
intRow = intRow + 1
Line Input #intHandle, strLine
varElements = Split(strLine, delim1)
For intCol = 0 To UBound(varElements)
.ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
.Selection = varElements(intCol)
Next intCol
Loop
Close #intHandle
.ActiveWorkbook.SaveAs filename:=ExcelFileName
.Quit
End With
Set appExcel = Nothing
End Function