VBA error when convert wrapped text file to excel

[jydbman]

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

 

[boblarson]

Well, I think part of your problem is your attempt to use CHR as a replacement for the column value in the range. If you look at this (which goes from 65 to 255) you will see that these don't correspond with Excel Column Headings after it gets past 90 (65+25):

65 A
66 B
67 C
68 D
69 E
70 F
71 G
72 H
73 I
74 J
75 K
76 L
77 M
78 N
79 O
80 P
81 Q
82 R
83 S
84 T
85 U
86 V
87 W
88 X
89 Y
90 Z
91 [
92 \
93 ]
94 ^
95 _
96 `
97 a
98 b
99 c
100 d
101 e
102 f
103 g
104 h
105 i
106 j
107 k
108 l
109 m
110 n
111 o
112 p
113 q
114 r
115 s
116 t
117 u
118 v
119 w
120 x
121 y
122 z
123 {
124 |
125 }
126 ~
127 
128 €
129
130 ‚
131 ƒ
132 „
133 …
134 †
135 ‡
136 ˆ
137 ‰
138 Š
139 ‹
140 Œ
141
142 Ž
143
144
145 ‘
146 ’
147 “
148 ”
149 •
150 –
151 —
152 ˜
153 ™
154 š
155 ›
156 œ
157
158 ž
159 Ÿ
160
161 ¡
162 ¢
163 £
164 ¤
165 ¥
166 ¦
167 §
168 ¨
169 ©
170 ª
171 «
172 ¬
173 ­
174 ®
175 ¯
176 °
177 ±
178 ²
179 ³
180 ´
181 µ
182 ¶
183 ·
184 ¸
185 ¹
186 º
187 »
188 ¼
189 ½
190 ¾
191 ¿
192 À
193 Á
194 Â
195 Ã
196 Ä
197 Å
198 Æ
199 Ç
200 È
201 É
202 Ê
203 Ë
204 Ì
205 Í
206 Î
207 Ï
208 Ð
209 Ñ
210 Ò
211 Ó
212 Ô
213 Õ
214 Ö
215 ×
216 Ø
217 Ù
218 Ú
219 Û
220 Ü
221 Ý
222 Þ
223 ß
224 à
225 á
226 â
227 ã
228 ä
229 å
230 æ
231 ç
232 è
233 é
234 ê
235 ë
236 ì
237 í
238 î
239 ï
240 ð
241 ñ
242 ò
243 ó
244 ô
245 õ
246 ö
247 ÷
248 ø
249 ù
250 ú
251 û
252 ü
253 ý
254 þ
255 ÿ

So, I think the fix is to use CELLS instead of RANGE:

[COLOR=#ff0000].ActiveSheet.Cells(intRow, intCol).Value = [/COLOR][COLOR=#000000][COLOR=red]varElements(intCol)
[/COLOR]

[/COLOR]
And by the way, you do NOT need to select in order to put values in.

 

[jydbman]

Sorry for responding late. I don't have time to try it until now since my friend just past away.

Thanks a lot for help. I changed the code as you suggested. Unfortunately, I still got the same error.

Run-time error '1004'; Application-defined or object-defined error.

highlight at this line in the function
.ActiveSheet.Cells(intRow, intCol).Value = varElements(intCol)
Thanks again!

Well, I think part of your problem is your attempt to use CHR as a replacement for the column value in the range. If you look at this (which goes from 65 to 255) you will see that these don't correspond with Excel Column Headings after it gets past 90 (65+25):


So, I think the fix is to use CELLS instead of RANGE:

[COLOR=#ff0000].ActiveSheet.Cells(intRow, intCol).Value = [/COLOR][COLOR=#000000][COLOR=red]varElements(intCol)
[/COLOR][/COLOR]

And by the way, you do NOT need to select in order to put values in.

 

[boblarson]

Not sure why you would be having an issue with that. I don't see anything that should be causing that error. Have you tried DECOMPILING and then recompiling.

Make sure to make a copy of the database file BEFORE Decompiling just in case it makes it worse.

Decompile instructions are here:
http://www.granite.ab.ca/access/decompile.htm

 

[jydbman]

Thank you for help. Decompile and recompile is new to me. Does it mean that any access database we develop will automatically be compiled?

I had not compiled the database and my version is access 2007.

I had attached the sample txt file for your reference. I still can not figure out why the function works for small txt file but not big txt file (with too many columns). Does it mean access can not recognize the text wrap?

Thanks again!

Not sure why you would be having an issue with that. I don't see anything that should be causing that error. Have you tried DECOMPILING and then recompiling.

Make sure to make a copy of the database file BEFORE Decompiling just in case it makes it worse.

Decompile instructions are here:
http://www.granite.ab.ca/access/decompile.htm

 

[jydbman]

The original tab delimited text file extension is .REC. If I manually open it with notepad, it still has text wrapped. If I manually open it with wordpad, there is no text wrap. The problem I have is I use the function to directly convert the .REC file to excel. I guess it opens it as notepad first and then convert to excel. Is there any way I can use access VBA to specify it to be opened using wordpad then convert to excel.

Any help will be greatly appreciated!

http://www.dbforums.com/editpost.php?do=editpost&p=6550751