User Prompt Excel Import to Access

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

Hi -

I am new at Access programming and I need some help.

I am trying to create a excel data import button that
does the following 3 functions:

- Prompts the user to select the excel file that needs to
be imported
- Prompts the user to select the Tab within the file that
needs to be imported
- Prompts the user to select the Access table in which
the file is to be imported into

Is this possible? If not, what would be the most logical
way to do this?

Please let me know!
Thanks!!!
 
Hi Leo,

Anotherone had a similar question which I have answered - try searching for FILNIGERIA in the General Questions newsgroup. There are several posts concerning this.

In addition i wrote some comments below.

--
HTH
Bernd



Leo said:
Hi -

I am new at Access programming and I need some help.

I am trying to create a excel data import button that
does the following 3 functions:

- Prompts the user to select the excel file that needs to
be imported
Click to expand...

If you want to browse through your drives, check:
First item in the 'API' section at the AccessWeb ...
www.mvps.org/access

If you want to have the user enter drive, file etc
XLFile = InputBox("Please enter Filename incl. Directories and drive : ")

- Prompts the user to select the Tab within the file that
needs to be imported
Click to expand...

With the following code you get the number of sheets in your Excel file :
SheetCount = XLapp.ActiveWorkbook.Sheets.Count

The names of the sheets you'll get with :
For n = 1 to SheetCount
SheetName(n) = XLapp.ActiveWorkbook.Sheets(n).Name
MessageText = MessageText & n & ".) " SheetName(n) & " "
Next n

XLapp needs to be defined first :
Dim XLapp As Object
and
Set XLapp = GetObject(, "Excel.Application")

With
SheetNumber = InputBox (MessageText)
you can ask the User to enter a number
- Prompts the user to select the Access table in which
the file is to be imported into
Click to expand...
It could be possible that there is something in Access to get a list of possible tables - unfortunately don't know at the moment - don't have Access at home ;-)
But you could use of course the InputBox as well. So that the User can enter the table.
TableName = InputBox ("Table ? ")

At the end you can use the
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, TableName, XLFile, True, XLapp.ActiveWorkbook.Sheets(SheetNumber).Name & "!a1..zz1000"
 
Hi Bernd

I did look up the messages but I think part of my
question is still unanswered. Maybe I should have been
more specific.

I have 2 worksheets in my file that will always been
named 'tbl_A' & 'tbl_B'.

I am looking to write a code in VB that will
automatically import these 2 worksheets into the 2 tables
in access also name 'tbl_A' & 'tbl_B'. However, I do not
have a specific range set (the number of columns in the
excel worksheets will always be the same but the number
of rows may vary).

Below is as far as I've managed to go on this:

Dim x As Integer
Dim filetoOpen As String

Standardizedfile = ActiveWorkbook.Name

'Find Standardized file

MsgBox "Please specify the location of the
Standardized File", vbOKOnly, "Locate File"
filetoOpen = Application.GetOpenFilename("Excel Files
(*.xls), *.xls", , _
"Please select the Standardized.xls file to link to")
If filetoOpen <> "" Then
FileName = Right(filetoOpen, Len(filetoOpen) -
InStrRev(filetoOpen, "\"))
On Error Resume Next
If IsError(Windows(FileName).Activate) Then
Workbooks.Open filetoOpen
Else
Windows(FileName).Activate
End If

DoCmd.TransferSpreadsheet , acImport,
acSpreadsheetTypeExcel8, XLapp.Standardizedfile.Sheets
(tbl_A), XLFile, True, XLSheet _

DoCmd.TransferSpreadsheet , acImport,
acSpreadsheetTypeExcel8, XLapp.Standardizedfile.Sheets
(tbl_B), XLFile, True, XLSheet _


End Sub

Any suggestions?

-----Original Message-----
Hi Leo,

Anotherone had a similar question which I have answered -
Click to expand...
try searching for FILNIGERIA in the General Questions
newsgroup. There are several posts concerning this.
In addition i wrote some comments below.

--
HTH
Bernd





If you want to browse through your drives, check:
First item in the 'API' section at the AccessWeb ...
www.mvps.org/access

If you want to have the user enter drive, file etc
XLFile = InputBox("Please enter Filename incl. Directories and drive : ")



With the following code you get the number of sheets in your Excel file :
SheetCount = XLapp.ActiveWorkbook.Sheets.Count

The names of the sheets you'll get with :
For n = 1 to SheetCount
SheetName(n) = XLapp.ActiveWorkbook.Sheets(n).Name
MessageText = MessageText & n & ".) " SheetName(n) & " "
Next n

XLapp needs to be defined first :
Dim XLapp As Object
and
Set XLapp = GetObject(, "Excel.Application")

With
SheetNumber = InputBox (MessageText)
you can ask the User to enter a number

It could be possible that there is something in Access
Click to expand...
to get a list of possible tables - unfortunately don't
know at the moment - don't have Access at home ;-)
But you could use of course the InputBox as well. So
Click to expand...
that the User can enter the table.
TableName = InputBox ("Table ? ")

At the end you can use the
DoCmd.TransferSpreadsheet acImport,
Click to expand...
acSpreadsheetTypeExcel8, TableName, XLFile, True,
XLapp.ActiveWorkbook.Sheets(SheetNumber).Name & "!
a1..zz1000"
 
BerHav wrote ...
With the following code you get the number of sheets in your Excel file :
SheetCount = XLapp.ActiveWorkbook.Sheets.Count
Click to expand...

Using automation to get a list of worksheet names is slow because you
have to open the workbook. The following is a link to some code that
uses ADO's OpenSchema method to get a list of worksheets from closed
workbook:

http://groups.google.com/[email protected]

Jamie.

--
 
HI Bernd -

Thanks for the comments below, my file open dialog box
works now.

However, I get this error message "ActiveX component
can't create object"

I think it has to do with the following statement, per
your instructions:

Dim XLapp As Object
Set XLapp = GetObject(, "Excel.Application")

Perhaps this is to do with my version of ACCESS? I'm
using Access Office 2000 Professional.

Please advise!
Thanks
-----Original Message-----
Hi Leo,

Anotherone had a similar question which I have answered -
Click to expand...
try searching for FILNIGERIA in the General Questions
newsgroup. There are several posts concerning this.
In addition i wrote some comments below.

--
HTH
Bernd





If you want to browse through your drives, check:
First item in the 'API' section at the AccessWeb ...
www.mvps.org/access

If you want to have the user enter drive, file etc
XLFile = InputBox("Please enter Filename incl. Directories and drive : ")



With the following code you get the number of sheets in your Excel file :
SheetCount = XLapp.ActiveWorkbook.Sheets.Count

The names of the sheets you'll get with :
For n = 1 to SheetCount
SheetName(n) = XLapp.ActiveWorkbook.Sheets(n).Name
MessageText = MessageText & n & ".) " SheetName(n) & " "
Next n

XLapp needs to be defined first :
Dim XLapp As Object
and
Set XLapp = GetObject(, "Excel.Application")

With
SheetNumber = InputBox (MessageText)
you can ask the User to enter a number

It could be possible that there is something in Access
Click to expand...
to get a list of possible tables - unfortunately don't
know at the moment - don't have Access at home ;-)
But you could use of course the InputBox as well. So
Click to expand...
that the User can enter the table.
TableName = InputBox ("Table ? ")

At the end you can use the
DoCmd.TransferSpreadsheet acImport,
Click to expand...
acSpreadsheetTypeExcel8, TableName, XLFile, True,
XLapp.ActiveWorkbook.Sheets(SheetNumber).Name & "!
a1..zz1000"
 
Hi Jamie -

Thanks for the note. In using the following statement:

Dim XLapp As Object
Set XLapp = GetObject(, "Excel.Application")

I get this error message "ActiveX component
can't create object"

I think it has to do with the following statement, per
Bernd's instructions:

Perhaps this is to do with my version of ACCESS? I'm
using Access Office 2000 Professional.

I went to http://www.mvps.org/access/bugs/bugs0007.htm to
download the patch but I am guessing it is not working as
I am using access 2000 (as opposed to access 97)

Please advise!
Thanks
 
Dim XLapp As Object
Set XLapp = GetObject(, "Excel.Application")
Click to expand...

GetObject only works if there is already an instance of Excel running.
To launch a new one if necessary, use something like

Dim XLApp as Object
Dim blXLisAllMine As Boolean

On Error Resume Next
blXLisAllMine = False
Set XLApp = GetObject(, "Excel.Application")
If XLApp Is Nothing Then
Set XLApp = CreateObject("Excel.Application")
blXLisAllMine = True
End If
If XLApp Is Nothing Then
MsgBox "Sorry, couldn't get hold of Excel."
Exit Sub
End If
On Error Goto 0
'If we're here, XLApp points to a running instance of Excel.
'Use blAllMine at the end of the procedure to decide whether
'or not to close the instance when you've finishsed with it.
 
...
In using the following statement:

Dim XLapp As Object
Set XLapp = GetObject(, "Excel.Application")

I get this error message "ActiveX component
can't create object"
Click to expand...

Another great reason for not using automation!

Check out that link I gave: it doesn't use automation.

Jamie

--
 
It's just ordinary programming. I'm sure that versions of this appear in
lots of books but I worked it out for myself one day when I needed to
solve the same problem you were facing.

Dude you rock! it works!
No amount of access reading would have taught me that....
-----Original Message-----
Dim XLapp As Object
Set XLapp = GetObject(, "Excel.Application")
Click to expand...

GetObject only works if there is already an instance of Excel running.
To launch a new one if necessary, use something like

Dim XLApp as Object
Dim blXLisAllMine As Boolean

On Error Resume Next
blXLisAllMine = False
Set XLApp = GetObject(, "Excel.Application")
If XLApp Is Nothing Then
Set XLApp = CreateObject("Excel.Application")
blXLisAllMine = True
End If
If XLApp Is Nothing Then
MsgBox "Sorry, couldn't get hold of Excel."
Exit Sub
End If
On Error Goto 0
'If we're here, XLApp points to a running instance of Excel.
'Use blAllMine at the end of the procedure to decide whether
'or not to close the instance when you've finishsed with it.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top