Page 1 of 12.
Results 1...20 of 225
Code: For i = 1 To foundFiles fileName = .foundFiles(i) DoCmd.SetWarnings False 'Import the summary information for this document to table docDetail DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "docDetail", fileName, True, "docDetail" 'Import progress of the actions for this document to table approvalProgress DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "approvalProgress", fileName, True, "approvalProgress" DoCmd.SetWarnings True Next iWhen I run this with the spreadsheet open both ranges import without errors and all is well. When the spreadsheet is closed (it's intended to pull data from large numbers of these sheets, so this needs to work) the first bit ('docDetail') imports fine, but the second fails with an Error 3270 'Property Not Found' error and no data imported.
Code: Private Sub import_Click() MsgBox "You are about to import the photographers work into this database. Please ensure that the staff database is renamed 'customers.xls' and saved on your desktop.." DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customers", "c:usersphotographerdesktopCustomers.xls", True DoCmd.Close DoCmd.OpenForm "Customers" End SubThe above has a few drawbacks, mainly that each user / pc has a different logon, so a different path. Each time I update or change the DB, I then have to go round the office and change the code on each PC. The other bad flaw is we cant see which file we are selecting, and I'm relying on people to manually name their xls file correctly.
Code: DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DrawingSheet", fileName, True, "DrawingSheet" 'fileName is a variable with the full path and filename of the excel workbookQuote: Run-time error '3011'; The MS Jet dB engine could not find the object 'DrawingSheet'. Make sure the object exists and that you spell its name and path name correctly. If I make "DrawingSheet" the first sheet and remove it from the statment it works, But I have to import 7 sheets to the database into different tables.
Code: Option Compare Database Public xlapp As New Excel.Application Public Sub ImportAll() Dim strPath As String Dim strFileName As String Dim strPass As String strPath = "G:CBT" 'Set Path strFileName = Dir(strPath & "*.xls") 'Set first file Do On Error GoTo ErrTrp DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" ErrTrp: If Err.Number = 3161 Then 'Encription error so unprotect workbook xlapp.Visible = False 'Open Excel xlapp.EnableEvents = False 'Disable Events (Macro's) xlapp.workbooks.Open strPath & strFileName 'Open File xlapp.ActiveWorkbook.Unprotect (strPass) 'Unprotect 'Try and Import again DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" xlapp.ActiveWorkbook.Protect (strPass) 'protect xlapp.ActiveWorkbook.Save 'Save xlapp.EnableEvents = True 'Enable Events xlapp.ActiveWorkbook.Close 'Close File xlapp.Quit 'Quit Excel Else End If strFileName = Dir() 'look for next file If strFileName = "" Then 'no more files Exit Do End If Loop End SubThanks in advance,
Code: DoCmd.TransferSpreadsheet acImport, 8, "portal_sgn", "U:old_usersportal_sgn.xls", True, "B2:E"When I try this it only imports 253 rows. However if the excel spread sheet is open during the import everything gets imported correctly.
Page 1 of 12.
Results 1...20 of 225