Hi, Marykay.
Is it possible to capture the values returned by the import wizard.
Sorry. Neither the Import Text Wizard nor the Import Spreadsheet Wizard
return any values for your procedure to capture.
In particular, the filename chosen and the talbe named given to the
imported data?
If you want to get the new table name, create a VBA procedure that checks
the current system time, then calls the Import Text Wizard or Import
Spreadsheet Wizard. When it's finished, iterate through the TableDefs
Collection to find the table name that was created after the time check.
For example, to save the imported table's name in a table, first create that
table to store the data:
1. Create a new query and paste the following into the SQL View pane, then
execute the query:
CREATE TABLE tblImportedTable
(ID COUNTER (1,1),
TableName TEXT (64) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (ID));
2. Paste the following VBA procedure into a standard module, then save and
compile:
Public Function getTblNameFrImpWz() As String
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim dtStart As Date
Dim idx As Long
Const IMP_ERR As Long = 13
dtStart = Now() ' Get current system date &
time.
RunCommand acCmdImport ' Call Import Text/SS wizard.
'--------------------------------------------------------------
' Find the one table created after this procedure started.
'--------------------------------------------------------------
Set db = CurrentDb()
For idx = (db.TableDefs.Count - 1) To 0 Step -1
Set tbl = db.TableDefs(idx)
If (tbl.DateCreated > dtStart) Then ' Ignore import err tbl.
If (Right$(tbl.name, IMP_ERR) <> "_ImportErrors") Then
'----------------------------------------------------------
' Clear any previous entries, then save the table name.
'----------------------------------------------------------
db.Execute "DELETE * FROM tblImportedTable;", dbFailOnError
db.Execute "INSERT INTO tblImportedTable (TableName) " & _
"VALUES ('" & tbl.Name & "');", dbFailOnError
getTblNameFrImpWz = tbl.Name
Exit For ' No need to check further.
End If
End If
Next idx
CleanUp:
Set tbl = Nothing
Set db = Nothing
Exit Function
ErrHandler:
If (Err.Number = 2501) Then ' User cancelled wizard.
' Do nothing.
Else
MsgBox "Error in getTblNameFrImpWz( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
GoTo CleanUp
End Function
If, however, you don't want to save the imported table's name in a table for
future use, then remove the lines of code (db.Execute) that deletes the data
from the tblImportedTable table and inserts the table name, and then the
function will only return the newly imported table's name to the calling
procedure.
Retrieving the existing table name that the data is imported into or getting
the path and file name of the file selected for import are both more complex
and involve more work than can be expected from free advice in a newsgroup
post, but the easiest way I can think of is to call the Windows File Open
dialog window and grab the path and file name from that, then pass it to the
Import Text Wizard or Import Spreadsheet Wizard and let the user follow the
wizard's prompts.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs:
www.DataDevilDog.BlogSpot.com,
www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
