Importing Text Files Using Macros

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
This is my current macros:

ActiveSheet.Unprotect
With ActiveSheet.QueryTables.Add(Connection:="TEXT;U:TOIMPORT.txt", _
Destination:=Range("A4"))
.Name = "TOIMPORT"
.FieldNames = True
.RowNumbers = False... etc. etc.

Instead of TOIMPORT (which is a re-naming), how can I get Excel to find a text file where the last few characters (in this case *used at.txt) are always the same. The text file is generated by another system (where the last few char. are the same). Can I use a wildcard? I tried the "*" and it didn't work.
This message was edited by ammdumas on 2002-04-23 11:53
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I dont have time to offer code right now, but I'll give you a hint. Use the dir() function to return each file according to specs in the directory.

dir("*.txt") would return the first .txt file. if you do a dir("*.txt") again in the code, it returns the next filename.

So what you want to do is set up an array of strings, and loop until dir("*.txt") returns NOTHING. Fill the array with the filenames, then process them in another loop.

If I get some time later I will try to help you further... Just ask.
 
Upvote 0
On 2002-04-23 12:24, ammdumas wrote:
Thanks, I'll give it a whack.

I forgot to mention that when using the dir("*.txt") command, you only specify the file specs the first time. To get the subsequent files you simply use dir() with no arguments.

Here is some code that fills an array with all text files. (Files_Array) It also displays a message box with each file it finds as it goes through them.<pre/>
Sub get_filenames()

Dim Files_Array() As String
Dim File_Temp As String
Dim File_Count As Integer

File_Temp = Dir("*.txt") 'First time DIR is used, specify file-specs

If File_Temp = "" Then
MsgBox "File(s) not found"
Exit Sub
End If

File_Count = 1
ReDim Preserve Files_Array(File_Count)

Do While File_Temp<> ""
File_Temp = Dir() 'To get the next file with the previous file
'specs, use dir() again, but with no arguments
If File_Temp<> "" Then
File_Count = File_Count + 1
ReDim Preserve Files_Array(File_Count)
Files_Array(File_Count) = File_Temp
MsgBox Files_Array(File_Count) & " Click OK for Next File"
End If
Loop

End Sub</pre>

You can then add a simple loop to this procedure calling your sub that processes each file:<pre/>
For i = 1 to File_Count
Call My_File_Processor(Files_Array(i))
next i</pre>


Oh, and one more note. This is all done in the currently active directory. To
change the path use the command:<pre/>
ChDir "Desired Path"</pre>


I hope that helped.
This message was edited by John McGraw on 2002-04-23 15:09
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top