Code: DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Calls", "d:My Documentsmyfile.xls", True, "details!b1:h1068"when trying to perform this code , it gives an error message that object do'snt in corect format , then i go to that workbook , save it as excell 97-2000 , then performing the code again , it success .
Code: Sub OpenSpecific_xlFile() Dim oXL As Object Dim oExcel As Object Dim sFullPath As String Dim sPath As String Set oXL = CreateObject("Excel.Application") On Error Resume Next oXL.UserControl = True On Error GoTo 0 On Error GoTo ErrHandle sFullPath = CurrentProject.Path & "myfile.xls" With oXL .Visible = True .workbooks.Open (sFullPath) .workbooks.SaveAs FileName:=(sFullPath), FileFormat:=56 End With ErrExit: Set oXL = Nothing Exit Sub ErrHandle: oXL.Visible = False MsgBox Err.Description GoTo ErrExit End Subbut it break at save as point , it said this proberty not allowed here
Code: Sub Link_To_Excel() 'Macro Loops through the specified directory (strPath) 'and links ALL Excel files as linked tables in the Access Database. Const strPath As String = "serverpathNew Files for upload" 'Directory Path Dim strFile As String 'Filename Dim strFileList() As String 'File Array Dim intFile As Integer 'File Number 'Loop through the folder & build file list strFile = Dir(strPath & "*.xlsx") While strFile "" 'add files to the list intFile = intFile + 1 ReDim Preserve strFileList(1 To intFile) strFileList(intFile) = strFile strFile = Dir() Wend 'see if any files were found If intFile = 0 Then MsgBox "No files found" Exit Sub End If 'cycle through the list of files & import to Access 'appending to tables called DealerLists and DealerContacts For intFile = 1 To UBound(strFileList) DoCmd.TransferSpreadsheet acImport, , _ "DealerLists", strPath & strFileList(intFile), True, "parts!A1:E229" DoCmd.TransferSpreadsheet acImport, , _ "DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2" Next MsgBox UBound(strFileList) & " Files were Imported" End Sub
Code: 'Read the number of lines in the file Dim xl As Excel.Application Dim xlsht As Excel.worksheet Dim xlWrkBk As Excel.Workbook Dim xlCell As Double Set xl = CreateObject("Excel.Application") Set xlWrkBk = GetObject(strPath & strFileList(intFile)) Set xlsht = xlWrkBk.Worksheets("Counters") Set xlCell = xlsht.cells(2, "A") DoCmd.TransferSpreadsheet acImport, , _ "DealerLists", strPath & strFileList(intFile), True, "parts!A1:E" & NumberOfLines Set xl = Nothing Set xlWrkBk = Nothing Set xlsht = Nothing Set xlCell = NothingWho can help me out in this?
Code: 'Add the view button appExcel.ActiveSheet.Buttons.Add(520, 110, 130, 25).Select appExcel.ActiveSheet.Shapes("Button 1").Select appExcel.Selection.Characters.Text = "Processing View" appExcel.VBE.ActiveVBProject.VBComponents.Import "C:BLAChange_Views_Script.bas" appExcel.ActiveSheet.Shapes("Button 1").Select appExcel.Selection.OnAction = "Change_Views"And when the file is generated, everything works fine.
Code: appExcel.ActiveWorkbook.SaveAs FileName:="C:BLAabc": FileExtStr = ".xlsb": FileFormatNum = 50The problem is when I close that generated file, and then I open it again. When clicking the button I get the message:
Code: DoCmd.RunSavedImportExport "123" DoCmd.RunSavedImportExport "456" DoCmd.RunSavedImportExport "789"So the problem would be the saved imports dont work for those 2 files since the worksheet name changed. So I was able to create a file in excel /w this macro...
Code: Sub Update() Const fPath As String = "C:AccessVolumes" Dim sh As Worksheet Dim sName As String Application.ScreenUpdating = False Application.DisplayAlerts = False sName = Dir(fPath & "*.xls*") Do Until sName = "" If Not sName = "UpdateSheets.xlsx" Then With GetObject(fPath & sName) .Sheets(1).Name = "1" .Close True End With End If sName = Dir Loop Application.ScreenUpdating = True Application.DisplayAlerts = True End SubSo now my problem is it is in excel... I don't know if it is even possible to write that up in access to be honest. I tried patching that up by doing this....
Code: Dim xl As Object Set xl = CreateObject("Excel.Application") xl.Workbooks.Open ("C:AccessVolumesUpdateSheets.xlsm") xl.Visible = True xl.Run "UpdateSheets.xlsm!Update" xl.ActiveWorkbook.Close (True) xl.Quit Set xl = NothingBut when I run this I get a runtime error 440 Automation Error /w
Code: Sub AutomatedDataPull() Dim strPathFile As String, strFile As String, strPath As String Dim blnHasFieldNames As Boolean Dim intWorksheets As Integer ' Replace X with the number of worksheets to be imported ' from each EXCEL file Dim strWorksheets(1 To 1) As String ' Replace X with the number of worksheets to be imported ' from each EXCEL file (this code assumes that each worksheet ' with the same name is being imported into a separate table ' for that specific worksheet name) Dim strTables(1 To 1) As String ' Replace generic worksheet names with the real worksheet names; ' add / delete code lines so that there is one code line for ' each worksheet that is to be imported from each workbook file strWorksheets(1) = "Test Program .108.100" ' Replace generic table names with the real table names strTables(1) = "Tracking" ' Change this next line to True if the first row in EXCEL worksheet ' has field names blnHasFieldNames = False ' Replace C:Documents with the real path to the folder that ' contains the EXCEL files strPath = "C:Access Test Docs" ' Replace X with the number of worksheets to be imported ' from each EXCEL file For intWorksheets = 1 To 1 strFile = Dir(strPath & "*.xls") Do While Len(strFile) > 0 strPathFile = strPath & strFile DoCmd.TransferSpreadsheet acImport, _ acSpreadsheetTypeExcel9, strTables(intWorksheets), _ strPathFile, blnHasFieldNames, _ strWorksheets(1) & "$" strFile = Dir() Loop Next intWorksheets End Sub
Code: DoCmd.TransferSpreadsheet _ acImport, _ acSpreadsheetTypeExcel5, _ "Table Name", _ Path & "Excel Data File.xls", _ TrueHowever, the difficulty is that some of the Excel files have more than one tab containing data. How do I specify which tab I want imported into which table? The Excel source files don't contain any named ranges, as the data in them is dynamic and number of rows changes from day to day. Is there a way of using VBA to import all the data from one specific tab into an Access table?
Code: Public Function Import_Inventory() Dim Msg As String, Button As Variant, Title As String, Response As Variant, fDialog As FileDialog Dim strFileName As String, XL As Object Msg = "Have you removed any duplicates and blank spaces from the file you are importing?" Button = vbYesNo + vbDefaultButton2 Title = "Import File" Response = MsgBox(Msg, Button, Title) If Response = vbNo Then Set fDialog = Application.FileDialog(msoFileDialogFilePicker) Set XL = CreateObject("Excel.Application") With fDialog .InitialFileName = "N:sharedLoriBSLR" .AllowMultiSelect = False .Title = "Please select file to edit" .Filters.Clear .Filters.Add "Excel", "*.xls" .Filters.Add "All Files", "*.*" If .Show = True Then strFileName = .SelectedItems(1) XL.Workbooks.Open strFileName XL.Visible = True End If End With Else Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .InitialFileName = "N:sharedLoriBSLR" .AllowMultiSelect = False .Title = "Please select file to import" .Filters.Clear .Filters.Add "Excel", "*.xls" .Filters.Add "All Files", "*.*" If .Show = True Then strFileName = .SelectedItems(1) DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM tblInventory" DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblTemp", strFileName, True DoCmd.RunSQL "INSERT INTO tblInventory SELECT * FROM tblTemp" DoCmd.DeleteObject acTable, "tblTemp" DoCmd.RunSQL "UPDATE tblInventory SET [Available]=0 WHERE [Available]