Import Excel Workbook from Sharepoint

[Jim]

Trying again, no response to my previous post - please help!

I have multiple Excel workbooks, most with multiple tabs, that I need to
import into an Access 2007 database on a regular basis. The Excel workbooks
are on Sharepoint. My database has tables set up with matching columns for
each worksheet. I'd like to set up a macro to import the workbooks into
Access. I've tried Transferspreadsheet unsuccessfully - can someone help?

Thanks!

 

[Jim]

I'm using code from Ken Snell's site that allows me to import all worksheets
from an Excel file on a Sharepoint site.
I got that code here:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpAllWktsSepTbl

I updated the path with the direct link for the Sharepoint workbook. This
is working great!

There are several workbooks I'll need to import from the same site, but not
all workbooks on the site are mine. Is there an easy way to add to this code
to add the path for each of the additional workbooks to be imported?

I have the code in the onclick event of a form. If there's no easier way, my
thought is to add a text box for each workbook to be imported, then put this
code in the On Got Focus Event with a MsgBox after the last one to indicate
the import was successful. I'd add a line of code to move the focus from one
to the next.

Just wondering if there's a better approach.

Thanks for the advice.
Jim

 

[Ken Snell MVP]

Jim, likely what you want can be done. But, to help me better understand
what you want to achieve, can you post the code that you're using and show
examples of what you wish the code would do that it's not.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jim]

Ken, when I hadn't gotten any responses in this group I posted my question in
the macro group and you responded there as well. Sorry for the duplicate
posts! I did post my code in the macro group and look forward to hearing
from you. I took your advice and renamed the controls sequentially, but
don't know how to adjust the code to loop through them and import the
associated worksheets.

Thanks again for your help!
Jim

 

[Ken Snell MVP]

Aah, ok. See my reply in macros ng.