Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to import multiple sheet of excel into Access database to a diffrent tables??
Hello
i am lookinng some unsocumented feture (earlier) of EXCEL,ACCESS to achive the following functionality. 1. Excel file is having multiple sheets say 10: 2. i want to download all the data into an Access database 3. output should have sheetname--tablename. I tried the below code (Which was an undocumented earlier) DoCmd.TransferSpreadsheet transfertype:=acImport, _ tablename:="tblsheet1", _ FileName:="A:\exceldata\excelbook1to10.xls", Hasfieldnames:=False, _ Range:="Sheet1!", SpreadsheetType:=9 When i executed from the VBA code, it is displaying the following error, Thanks in advance. baka@tokyo japan Execution error Worksheet format variable value is not correct... etc(translated from japanese ) Does any one show me the way to get all the sheet should becomes tables in access Here is the code i was using in VBE (part of the code is changed for web viewing purpose) ------------------------------------------ Private Sub import_Click() catlog.ActiveConnection = CurrentProject.Connection For Each tble In catlog.Tables If tble.Name = "table001" Then MsgBox "table001 allreday exists", vbOKOnly + vbInformation, "confirm please" Exit Sub End If Next tble , the below 5 lines code is giving an error DoCmd.TransferSpreadsheet transfertype:=acImport, _ tablename:="tblsheet1", _ FileName:="A:\exceldata\excelbook1to10.xls", Hasfieldnames:=False, _ Range:="Sheet1!", SpreadsheetType:=9 ' ' the below code working for a first sheet ' For Each tble In catlog.Tables ' If tble.Name = "table001" Then ' MsgBox "table001 allreday exists", vbOKOnly + vbInformation, "confirm please" ' Exit Sub ' End If ' Next tble ' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "table001", "A:\exceldata\excelbook1to10.xls", True, "" End Sub ----------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to import data from daily reports (excel) into excel database | Excel Discussion (Misc queries) | |||
Linking workbook and range protected Excel sheet to Access | Excel Discussion (Misc queries) | |||
Import from Access to Excel, via pivot table | Excel Discussion (Misc queries) | |||
Access tables in Excel | Excel Discussion (Misc queries) | |||
Excel graphs with Access database | Excel Discussion (Misc queries) |