I'm trying to use the method that was described in one of the other threads that I saw posted on stackoverflow here.
When using the method that was described in that thread (that got the green check) I'm getting an error when running the code. The error pop-ups up a blank message box with no contents.
A couple of things to mention:
(1) I've made sure to select and activate the Microsoft Access 14.0 Object Library in Excel.
(2) I am running the sub procedure from my database worksheet in Excel.
(3) I am then running the AccImport procedure within my code procedure from my wizard worksheet in Excel (separate worksheet).
EXCEL SPREADSHEET SETUP
I can't use screenshots as of yet as I am new to the community but the database worksheet field range is setup as follows.
B1 (Occurrence Date), C1 (Machine), D2 (Cell), E2 (Status), F2 (Issue), G2(Preventative/Corrective), H2 (Assigned To)
B2 (15-APR-2015), C2(machine1), D2(cell1), E2 (0), F2(Test), G2 (Corrective), H2 (nameexample1)
ACCESS DATABASE TABLE IS SETUP AS FOLLOWS:
Table Name: MaintenanceDatabase
ID, Occurrence Date, Machine, Cell, Status, Issue, Preventative/Corrective Assigned To
Here is the code that I am running from the Database worksheet in Excel:
Sub AccImport() Dim acc As New Access.Application acc.OpenCurrentDatabase "C:\Users\brad.edgar\Desktop\DASHBOARDS\MAINTENANCE\MaintenanceDatbase.accdb" acc.DoCmd.TransferSpreadsheet _ TransferType:=acImport, _ SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _ TableName:="MaintenanceDatabase", _ Filename:=Application.ActiveWorkbook.FullName, _ HasFieldNames:=True, _ Range:="Database$B1:H2" acc.CloseCurrentDatabase acc.Quit Set acc = Nothing End Sub
Code Snippet from other Worksheet Object that Runs AccImport:
Public Sub DeleteSelectedRecord() Dim CurrentSelectedIndex As Integer ' Assign the currently selected index to CurrentSelectedIndex CurrentSelectedIndex = [Database.CurrentIndex] ' Move the ListBox Selector If [Database.CurrentIndex].Value = [Database.RecordCount] Then 'Last item on the list [Database.CurrentIndex].Value = [Database.CurrentIndex].Value - 1 End If 'Copy to Access Database Database.AccImport ' Delete the entry Database.ListObjects("Database").ListRows(CurrentSelectedIndex).Delete End Sub
Hopefully someone could shed some light into why I'm getting an error.
Thanks in advance for any help.