How can I have Access prompt the user to select a text file to imp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am atrying to automate the importing of multiple $ delimited text files.
The problem is that the files get new names each time they are released,
although they follow a basic pattern. I'd like to write a macro that limits
user involvement to selecting the appropriate files. A prompt such as,
"Please select the DEMO file" would be perfect. The files will be appended
to existing tables.

The TransferText macro and method both require hard-coding the file names,
as far as I can tell. Any thoughts?
 
Thanks, Doug, that looks great. My total lack of VBA and Access experience
has made this task harder than I thought, however. Please excuse my naivete.

The question now is: how do I write the whole thing? I don't even know how
to run the script. I think eventually I will have to link it to a button in
a form. In the interim, I have this:

(http://www.mvps.org/access/api/api0001.htm)

------------------

**How do I initiate a routine? Sub...? **

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select the DEMO file...", _
Flags:=ahtOFN_HIDEREADONLY)

Do.Cmd TrasnferText acImportDelim, **How do I call a spec?**,,
strInputfileName

End Sub

------------------

That's it.

I need to open FIVE files.

Thanks in advance for any more help.
 
I generally avoid using the API reference that Doug mentioned because
of the complexity of it. Office has a file dialog object that is much
easier to use. In order to use it, you will need to go into you VBA
window, go to the Tools menu, to References. In there scroll down to
"Microsoft Office 11 Object Library" and check it. The libraries are
in alphabetic order so it should be a ways down the list. Then you can
use this code

Private Sub Command0_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to select multiple files in the dialog box.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Please Select the Demo File"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Text Files", "*.txt"

If .Show = True Then
For Each varFile In .SelectedItems
'I'm not sure where you are importing these into,
'but if you explore help on how the docmd.transfertext
'method works you should be able to figure it out
DoCmd.TransferText acImportDelim, , "TableName", varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub
 
I generally avoid using the API reference that Doug mentioned because
of the complexity of it. Office has a file dialog object that is much
easier to use. In order to use it, you will need to go into you VBA
window, go to the Tools menu, to References. In there scroll down to
"Microsoft Office 11 Object Library" and check it. The libraries are
in alphabetic order so it should be a ways down the list. Then you can
use this code:

Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to select multiple files in the dialog box.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Please Select the Demo File"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Text Files", "*.txt"

If .Show = True Then
For Each varFile In .SelectedItems
'Code to use files goes in here
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

To use this, create a command button on your form, right click on it,
choose Build Event. In the next window, choose Code Builder. You
should see something along the lines of:

Private Sub Command01_Click()

End Sub

You will want to paste the code from above into the middle of those two
lines. The part that you will have to adjust for your needs is the:

For Each varFile In .SelectedItems
'Code to use files goes in here
Next

What that code does is go through each file the user selected. In the
middle you will need to tell Access what to do with the file. If you
are importing all the files into a table, you will want to explore the
DoCmd.TransferText method in VBA. Do a search for that in help in the
VBA window and it should guide you. If not, post more information on
how you want Access to deal with the files the user selected and we can
help.
 
Wow, thanks a bunch. This is greatly helpful. I reiterate that I am a
newbie, so your step by step instructions are much appreciated.

Here is the whole scenario.

Each quarter, the FDA releases a file that contains all of the side effects
reported for every drug on the market. The database contains 7 text
documents, 5 of which I am interested in importing into access so that I can
query them.

The files are

1. DEMOyyQq.TXT

2. DRUGyyQq.TXT

3. REACyyQq.TXT

4. OUTCyyQq.TXT

5. RPSRyyQq.TXT

I want to import the 5 files into 5 tables (that have predefined
relationships) automatically. Since I don't know what the yyQq portion of
the name looks like in advance, I need to be able to select all 5 manually.
Then I will query the newly updtated database to see what's happening
regarding a handful of drugs that I am tracking.

Ultimately, I'd like to automate the queries as well. Right now I have a
different query for each drug, but it'd be great to create a form from which
I could

1) Import the latest files

2) Search for any drug

3) Search for any outcome

4) Export the results to Excel

And that's the story. You can download the .txt files in question at
http://www.fda.gov/cder/aers/extract.htm. I would prefer not to deal with
Access, but it's the only program that I have that can deal with as many
records as there are in these files.

Thanks again. --Joel
 
This code below, if the tables and import specifications are setup
before running it, will import these five files if the user selects all
five files.

Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim strFileName As String

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to select multiple files in the dialog box.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Please select the files"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Text Files", "*.txt"

If .Show = True Then
For Each varFile In .SelectedItems
strFileName = Mid(varFile, InStrRev(varFile, "\") + 1)
Select Case Left(strFileName, 4)
Case "demo"
DoCmd.TransferText acImportDelim, "DemoSpec",
"DemoTable", varFile, True
Case "drug"
DoCmd.TransferText acImportDelim, "DrugSpec",
"DrugTable", varFile, True
Case "reac"
DoCmd.TransferText acImportDelim, "ReacSpec",
"ReacTable", varFile, True
Case "outc"
DoCmd.TransferText acImportDelim, "OutcSpec",
"OutcTable", varFile, True
Case "rpsr"
DoCmd.TransferText acImportDelim, "RpsrSpec",
"RpsrTable", varFile, True
End Select
Next
Else
MsgBox "You canceled the import."
End If
End With

In order for this to work correctly, you will have to set up your
tables and import specifications ahead of time. If you are unfamiliar
with text imports in Access, I'd recommend you do a little research on
text imports, import specifications and the transferText method.
 
This code below, if the tables and import specifications are setup
before running it, will import these five files if the user selects all
five files.

Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim strFileName As String

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to select multiple files in the dialog box.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Please select the files"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Text Files", "*.txt"

If .Show = True Then
For Each varFile In .SelectedItems
strFileName = Mid(varFile, InStrRev(varFile, "\") + 1)
Select Case Left(strFileName, 4)
Case "demo"
DoCmd.TransferText acImportDelim, "DemoSpec",
"DemoTable", varFile, True
Case "drug"
DoCmd.TransferText acImportDelim, "DrugSpec",
"DrugTable", varFile, True
Case "reac"
DoCmd.TransferText acImportDelim, "ReacSpec",
"ReacTable", varFile, True
Case "outc"
DoCmd.TransferText acImportDelim, "OutcSpec",
"OutcTable", varFile, True
Case "rpsr"
DoCmd.TransferText acImportDelim, "RpsrSpec",
"RpsrTable", varFile, True
End Select
Next
Else
MsgBox "You canceled the import."
End If
End With

In order for this to work correctly, you will have to set up your
tables and import specifications ahead of time. If you are unfamiliar
with text imports in Access, I'd recommend you do a little research on
text imports, import specifications and the transferText method.
 
Interesting. I always avoid the built-in file dialog object, because it's
just one more reference than can go wrong.

As well, some parts of the File Dialog don't work (File Save, if memory
serves)

I don't see how what you've got is any less complex than using the API. Once
you've got the basic functions imported into your application, you need 2
lines of code (4 if you count the declarations!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing Text Files Into Access 1
TransferText question 2
Access Query - Union of TEXT files that are selected. 8
Multiple TransferText to the same File - Need to Append not Overwrite 2
I can't specify the Specification Name when use transfertext 1
Prompt User to Select Macro to Run? 1
Get selected word documents from user's choice and merge those documents as a single doc file 0
automate open text file 9

Back
Top