exporting an excel file into an access table

  • Thread starter Thread starter asset
  • Start date Start date
A

asset

Hi,
I have created a button on a form. The purpose of which, is to export an
Excel file (a table with column headings) held in a particular folder within
my directory, to an access table held in another folder of another computer
within the network.

I have tried using the DoCmd.TransferSpreadsheet but to no avail.

I would be grateful for any suggestions.
 
The TransferSpreadsheet should do exactly what you want. Post what you have
tried and what result you are getting. If you are getting an error message,
post the error number and description. With more detail, we can help you
make this work.
 
sorry how do you do this??

Klatuu said:
The TransferSpreadsheet should do exactly what you want. Post what you have
tried and what result you are getting. If you are getting an error message,
post the error number and description. With more detail, we can help you
make this work.
Click to expand...
 
To do what you want is simple. First of all close the Excel workbook. Open
the Access database, click on File-->Get External Data-->Import. On the
bottom of the resulting dialog box, select Microsoft Excel (*.xls) and
navigate to the folder your spreadsheet resides. The rest is simple; just
follow the instructions.

Sam
 
OfficeDev18 said:
To do what you want is simple. First of all close the Excel workbook. Open
the Access database, click on File-->Get External Data-->Import. On the
bottom of the resulting dialog box, select Microsoft Excel (*.xls) and
navigate to the folder your spreadsheet resides. The rest is simple; justutton
follow the instructions.

Sam
Click to expand...
Hi,
Thanks for all your comments.

Perhaps I didnt explain my intentions well enough.

I'm trying to attach some VB code to the button as an event procedure.

My difficulty lies in when i try to implement the DoCmd transfersheet
function when I transfer the excel sheet from an access program (form with a
button) wiyhin my PC to anther PC which houses the Access database/table, i.e.
across the network. I'm using Access97.

How do I point to the Access Table of the database which is on the other PC.
I've tried
\\name of computer\folder\*.xls

All this is doing is creating a table with that path as its name in my own
Access database?
 
Sorry, I misunderstood.

What you need to do is read the following two topics in the help file:

FileSystemObject
CopyFile method

If I had more time I'd elaborate. Unfortunately I don't this minute.

Please tell me if this helped.

Sam
To do what you want is simple. First of all close the Excel workbook. Open
the Access database, click on File-->Get External Data-->Import. On the
Click to expand...
[quoted text clipped - 3 lines]
Sam
Click to expand...

Hi,
Thanks for all your comments.

Perhaps I didnt explain my intentions well enough.

I'm trying to attach some VB code to the button as an event procedure.

My difficulty lies in when i try to implement the DoCmd transfersheet
function when I transfer the excel sheet from an access program (form with a
button) wiyhin my PC to anther PC which houses the Access database/table, i.e.
across the network. I'm using Access97.

How do I point to the Access Table of the database which is on the other PC.
I've tried
\\name of computer\folder\*.xls

All this is doing is creating a table with that path as its name in my own
Access database?
Click to expand...
 
Thanks SAM

Read it , but it seems this just copies the file to the required destination.

The problem for me is exporting the Excel file from my PC
and appending ( or importing ) this data into an Access97 database table held
in another
location. Its not just a question of copying the file to another PC.

This entire operation would take place by clicking on a command button
located in
my Access form designed especially to carry out this operation.



Sorry, I misunderstood.

What you need to do is read the following two topics in the help file:

FileSystemObject
CopyFile method

If I had more time I'd elaborate. Unfortunately I don't this minute.

Please tell me if this helped.

Sam




[quoted text clipped - 20 lines]
All this is doing is creating a table with that path as its name in my own
Access database?
Click to expand...
Click to expand...
 
OK. In that case, you want to do the following. Link or import the Excel file.
You also need the following code:

Dim dbNew As Database

Set dbNew=OpenDatabase ("path and remote database name here in quotes",
there's more that's necessary, including passwords. See the OpenDatabase
method for details. Make sure to read the example for necessary detailed
coding.

Once you have the remote db opened, use an append query to do its job.
Close all.

Helpful?

Sam
Thanks SAM

Read it , but it seems this just copies the file to the required destination.

The problem for me is exporting the Excel file from my PC
and appending ( or importing ) this data into an Access97 database table held
in another
location. Its not just a question of copying the file to another PC.

This entire operation would take place by clicking on a command button
located in
my Access form designed especially to carry out this operation.
Sorry, I misunderstood.
Click to expand...
[quoted text clipped - 8 lines]
Sam
To do what you want is simple. First of all close the Excel workbook. Open
the Access database, click on File-->Get External Data-->Import. On the
Click to expand...
[quoted text clipped - 20 lines]
All this is doing is creating a table with that path as its name in my own
Access database?
Click to expand...
Click to expand...
Click to expand...
 
Actually, having said the above, I realize there's a much simpler solution.
Open a New query in design mode. In the query design toolbar, change the
query to an APPEND query. In the resulting dialog box, select "another
database", and select the remote database and table. You will need to provide
any passwords, etc. as well. Then you can go ahead and design the appending
of data in the QBE grid in the usual manner.

Sam
OK. In that case, you want to do the following. Link or import the Excel file.
You also need the following code:

Dim dbNew As Database

Set dbNew=OpenDatabase ("path and remote database name here in quotes",
there's more that's necessary, including passwords. See the OpenDatabase
method for details. Make sure to read the example for necessary detailed
coding.

Once you have the remote db opened, use an append query to do its job.
Close all.

Helpful?

Sam
Thanks SAM
Click to expand...
[quoted text clipped - 20 lines]
Click to expand...
 
Hi,

Thanks,
This second solution worked well. Must now play around with it a bit more.
But it certainly helps.

In fact rather than creating the excel files i can now import the tables
directly from the database to the remote PC and append them.

It still leaves me with the doubt of how to import excel files from one pc to
another and appending them to a separate database.??

But thanks anyway your solution was quite simple ... y didnt i think of that!!
!!!!!

regards

Actually, having said the above, I realize there's a much simpler solution.
Open a New query in design mode. In the query design toolbar, change the
query to an APPEND query. In the resulting dialog box, select "another
database", and select the remote database and table. You will need to provide
any passwords, etc. as well. Then you can go ahead and design the appending
of data in the QBE grid in the usual manner.

Sam
OK. In that case, you want to do the following. Link or import the Excel file.
You also need the following code:
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
Back
Top