Importing an Excel Worksheet to a Table in Access

[Guest]

Using Access 2000, I'm trying to import a worksheet from Excel into a table
in Access. Manually, I have no problem and the data imports just fine. I
also found a way to import a particular worksheet from a particular file
using the "Transfer Worksheet" function and it's "filename" and "range", but
the path name is static (same file name each time the function is called).

How would I import a specific worksheet in multiple Excel files (ie, the
second worksheet in each of 10 separate Excel files) into an Access table by
choosing the file via the "Open File Dialog"? I'd like to be able to control
the event via the switchboard I have set up...I push the button to import a
worksheet, an Open File browser appears, I select the file I want, and the
table is appended with the worksheet data...

Any ideas?

 

[Joe Fallon]

This may help:

For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With

 

[Douglas J. Steele]

Just to tag along on Joe's post. While the FileDialog method he mentions
does work fine for Access 2002 and 2003, AFAIK it cannot be used with the
runtime, so if there's any chance that you're going to be going that route,
you're best off sticking with the API approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This may help:

For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With

--
Joe Fallon
Access MVP

Using Access 2000, I'm trying to import a worksheet from Excel into a
table
in Access. Manually, I have no problem and the data imports just fine. I
also found a way to import a particular worksheet from a particular file
using the "Transfer Worksheet" function and it's "filename" and "range",
but
the path name is static (same file name each time the function is
called).

How would I import a specific worksheet in multiple Excel files (ie, the
second worksheet in each of 10 separate Excel files) into an Access table
by
choosing the file via the "Open File Dialog"? I'd like to be able to
control
the event via the switchboard I have set up...I push the button to import
a
worksheet, an Open File browser appears, I select the file I want, and
the
table is appended with the worksheet data...

Any ideas?