Importing Multiple Worksheets into Multiple Tables

[blee]

I am trying to import a spreadsheet, which contains about 100 worksheets. Instead of using the import wizard for every worksheet, I was hoping to import them all in one go. Also, each worksheet should be imported into a new table. Can anyone advise on how this can be done.<br><br>Thanks in advance<br><br>Blee

 

[DougP]

Is this a one time deal or do you have to do it over and over? <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[blee]

Just have to do this once. I was hoping to have the worksheet names as my access table names.<br><br>Blee

 

[DougP]

Well some case's are not a smooth as we would like.<br>Access has a problem with multiple TABs. It wil do it manually but not with VBA code. So you just have to &quot;bit the bullet&quot; and import them one at a time. <br>Then rename or specify the name as it's imported.<br>Since you only have to do this once. I would just do it and not woory about making a routine.<br>It will go pretty quick once you're done several.<br>And if you have an underlying or partner you can pick do different ones which will make it go twice as fast.<br>Or do what I call a class lession where several people and yourself are doing a step by step Import with you calling the shots. Like so &quot;Class, Right Click on the Tables TAB&quot;,<br>&quot;Click Import&quot;, &quot;Is everybody with me&quot; <br>Mike: &quot;Uh, no, I don't know what a Tables TAB is&quot; <br><br>etc etc<br><br>OK<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[famousb]

actually, you can use code to designate which worksheet (or TAB) in a spreadsheet to import, problem is, you'd have to have 100 lines of code to designate the 100 different worksheets, and then if you misstype something, etc...<br>it's kind of a lesser of two evils thing. do you want to type the code, or do you want to do the import thing?<br><br>you could actually jsut use the code, and just type the names of the worksheets for each one.&nbsp;&nbsp;it would look like this:<br><b><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Message, Default, MyValue As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Title<br>&nbsp;&nbsp;&nbsp;&nbsp;Message = &quot;Enter the worksheet name.&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Default = &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;MyValue = InputBox(Message, Title, Default)<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, &quot;MyValue&quot;, &quot;PathAndFileNameOfSpreadsheet&quot;, 0, &quot;MyValue&quot;<br></b><br><br>i think this should work for you.&nbsp;&nbsp;the &quot;0&quot; designates whether you have field names or not, if you do, make it -1.<br><br>the only thing you may have trouble with is the last part of the statement which says MyValue.&nbsp;&nbsp;this sets the worksheet to the name you designated, but also wants a range in the excel file.&nbsp;&nbsp;usually in the format &quot;Worksheet!start:end&quot;&nbsp;&nbsp;i just left off the second half - it should work, but may need some tweaking.<br><br>you could also put in a loop so you don't have to click a button 100 times, but this gives you the freedom to take a break when needed.<br><br>let me know if you need more ideas on this line.<br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>