MSAccess97:import multiple excel wrksheets using VBA code? 1

[sherree]

Hi,<br><br>I'll greatly appreciate any help or suggestions on the following questions:<br>How do I import multiple excel worksheets <br>into an Access table use VBA code only?<br>I've tried this code:<br>DoCmd.TransferSpreadsheet acImport, 8, &quot;spreadsheet&quot;, &quot;w:abc.xls&quot;, True<br>but only imports one worksheet at a time, please help!<br><br>I want the user to enter the excel file name <br>using this format:<br>'****** SETS THE USER PROMPT&nbsp;&nbsp;**************<br>'Dim Message, Default, MyValue As String<br>'Dim Title<br>'Message = &quot;Enter the Excel File name including driveath&quot; 'Set Prompt<br>'Default = &quot;&quot;<br>'MyValue = InputBox(Message, Title, Default)<br>'*******************************************<br>But if I use the TransferSpreadsheet Method<br>the file name is a required argument, help!

 

[Liberty]

What if you use a loop to go through the user inputs. Or with the TransferSpreadSheet method specify a wildcard expression to get all the files.

 

[famousb]

ok use this,<br><b><br>Dim Message, Default, MyValue As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Title<br>&nbsp;&nbsp;&nbsp;&nbsp;Message = &quot;Enter the Excel File name including driveath&quot; 'Set Prompt<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;YourTableName&quot;, MyValue<br></b><br><br>this puts the pathname in that the user input as the filename.<br><br>if you want to do a set amount of times, you could simply repeat the statement and use a MyValue1, MyValue2, MyValue3, etc...<br><br>let me know if you need more help <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[sherree]

Brian Famous<br><br>I appreciate your help but your code was unable to help me.<br>I recieve an error message Microsoft Jet database Engine could not find the<br>object 'f:\excelspreadsheets ' Make sure the object exists and that you spell<br>its name and path name correctly.&nbsp;&nbsp;The file name is correct. Do you have any<br>more suggestions.<br>Here is my code:<br><br>Private Sub ReadFile_Click()<br>On Error GoTo Err_ReadFile_Click<br><br>'****** SETS THE USER PROMPT ****************<br>Dim Message, Default, MyValue As String<br>Dim Title<br>Message = &quot;Enter the Excel File name including driveath&quot;<br>MyValue = InputBox(Message, Title, Default)<br><br>DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, &quot;spreadsheet&quot;, &quot;MyValue&quot;&nbsp;&nbsp;&quot;***&quot;MyValue&quot; THE FILE NAME IS A REQUIRED ARGUMENT (eg., F:\EXCELSPREADSHEETS)<br>&nbsp;&nbsp;&nbsp;DoCmd.Close<br><br>MsgBox &quot;File Processing Completed...&quot;<br><br>Exit_ReadFile_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>Err_ReadFile_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.DESCRIPTION<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_ReadFile_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br>Thanks,<br>Sherree

 

[famousb]

Sherree, <br>When you type in your file name when prompted, you also have to type in the file extension.&nbsp;&nbsp;i've tried both this sample and the sample i gave to Dan - they both work.<br>when you are prompted, type:<br>F:\ExcelSpreadSheets.xls<br>or <br>F:\ExcelSpreadSheets\yourfilename.xls<br><br>but you must type the full name <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>