Macro Help - Import External Data from Text File

[bobdole22]

I manually click on the "External Data" tab and link in a text file for my needed project. Is it possible to automate this in a macro? From what I've seen it is not, but I wanted to make sure and ask some professionals before I scrapped the idea.

Any help is greatly appreciated. Thanks!

[tfurnivall]

A lot of it depends on what you want to do with the contents of the file. Assuming that the contents represent lines (1 line= 1 record), with ClearlyDistinguishableData (eg comma-delimited, then it's fairly easy.

You'll need a FileDialogBox to select the file - this flexibility is, presumably, why you want to do this using VBA - and a reference to the Microsoft Office Object LIbrary. You can add this by using Tools|References... in the VBA environment, and looking for the Microsoft Office Object Model. (Choose the lates one that's available). You'll need to define a FileDialog:


Dim fDialog As Office.FileDialog



and then create it, and set some parameters:


Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

fDialog.allowMultiSelect = False
fDialog.Title = "Pick the file you want to use"
fDialog.Show

If fDialog.selecteditems.Count > 0 Then
GetControlFileName = fDialog.selecteditems(1)
End If


I have this code running in a developing app, so I think that the distinction between Office.FileDialog and Application.FileDialog is relatively OK (I'll find out soon, for sure!)

This gives you a filename, and you can then Open the file and read it, parsing the lines as you go.

I'm hoping that Opening the file, Reading the lines and Parsing them will be familiar to you. If not, you might want to check any VBA manual for Open <Filename> as #<filenumber>, and the Split function.

Let us know if this helps - if it's something you already know, then just ignore it (it's worth what you paid for it ;-) and re-post with more detail on what we can do to help you!

Good luck,

Tony

[bobdole22]

Thank you for the reply. So your suggesting VBA through Access. That's a good idea. What is awkward is my boss has this running through an Excel program already.

Let me explain, He opens excel, runs some Macros to create tables, closes it, then prints some SQL lines from those tables. But now he wants me to add in a way to do it for different Database text files. If I can do it in a Access macro I can just add it to the list of Macros I run.

Doesn't seem possible though does it. What about the Macro RunCode? Could this be of any help here?

[tfurnivall]

Ahah! I assumed that since this was on an Access help forum that's the technology you wanted to use!
Here's a snippet that I use in Excel, which gets you a filename.



Function GetFileToScan(DirName As String) As String

With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = Application.DefaultFilePath + "\"
.Title = "Please select the directory to scan"
.Show
If .SelectedItems.Count = 0 Then
GetFileToScan= ""
Else
GetFileToScan= .SelectedItems(1)
End If
End With

End Function



Assuming then, that the file contains lines of delimited fields, you would move through the target spreadsheet, one row at a time, using the TextToColumns function.

Here's a sample that I managed to get by simply recording a macro as I stepped through the process (Good technique to learn!)


Sub TextToColumns()
'
' TextToColumns Macro
'

'
Range("A1").Select
Selection.TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True
End Sub



You'll need to do some learning on the TextToColumns function, but the skeleton of the code is right there.

HTH

Tony

[bobdole22]

Okay, great!

And yes, btw the file is delimited perfectly with commas. So will TexttoColumns print out the text file on my excel file? Or am I off here?

Also, I love the recording macro feature, that is how I have learned a ton of stuff! Thanks for the help so far.

[tfurnivall]

The TextToColumns function won't 'print' anything - it will however, split the file into columns, where each column contains a delimited value from the file. This link, http://msdn.microsoft.com/en-us/library/office/aa299786%28v=office.10%29.aspx, will give you a lot more information about the function.

Tony

[bobdole22]

Okay great, I didn't want it to print anything out. I want it to update the data in Access. This seems exactly like what I am looking for. I'm going to try it out for a while now. I'll probably have some more questions one day. Haha.

Thanks!