Excel VBA to Export Data to MS Access Table - Extended

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.

Cheers,

Brad

Excel Trap a Window close?

Is there no way in Excel VBA to detect if a user attempts to close a (certain) window? There is a deactivate event, but that's not the same thing, neither is closing the entire workbook. I'd like to trap an attempt by the user to close a specific window.

need help for pulling website tables to excel

hey guys im new to this and i am having a real hard time finding a way to pull tables off the website to excel. the website that i have access to has a login system with a one time password(OTP) which will be sent to my mobile. is there a way to do it so that i can automate the login and pause for me to enter my OTP then continue the pulling?

i have tried pulling tables off other open websites but this OTP is making it really difficult to work around.

thank you for your time and i really appreciate it if you could help me.

Date Sorting in Excel PivotTables

I have a PivotTable that has a date field in it, call it Mydate.

I have put this as a column label and then grouped the label by Years, Months and Days.

I then have put the years and days in reverse order by clicking on the label and then choosing to Sort Newest to Oldest.

If I then expand the month to show the days and then try to sort the days, the sorting is messed up....it treats the days above 10 as coming before day 2, 3, etc.

Here is what it looks like in the row label section:

    -2015  
         -Apr
             9-Apr
             8-Apr
             ...
             2-Apr
             1-Apr
             16-Apr
             15-Apr
             ...

Is there any way to fix this in the PivotTable or do I need to make an additional "Day" column to get this to work properly?

EDIT: These are properly stored as dates and the sorting works properly if I Ungroup the date field and then sort on just the dates without Years or Months.

How do I call up data from multiple excel files into one constantly updated file?

EDIT: I think this question belongs over at superuser not here at Stackexchange.

What I would like to do is have a single excel file that calls up data from every excel file in a given directory. Specifically if I have a time sheet excel file from multiple people working multiple different job numbers I would like to have that data populated in a single file for everyones times. The directory where the files are stored would be updated weekly so I would want the "master" excel file to reflect the weekly changes automatically...hopefully. Is there an easy way to do this that I would be able to teach someone else?

How to import an excel file into a database table as binary type by using SQL Server Management Studio

I have an excel file. I just would like to import the excel file into a table ExcelFiles without using c# code.

ExcelFiles has 3 columns

ExcelId, ExcelName,    ExcelFile
int       archar(50),  varbinary(max)

I would like to import excel file into this table through SQL Server Management Studio without using c#.

Is this possible?

Generating String from Excel Sheet

I have an excel with three columns and three rows like below:

Field1,Field2,Field3

Row1Value1,Row1Value2,Row1Value3

Row2Value1,Row2Value2,Row2Value3

Row3Value1,Row3Value2,Row3Value3

I want to write a VBA Macro to generate an output like below:

Field1=Row1Value1,Field2=Row1Value2,Field3=Row1Value3
Field1=Row2Value1,Field2=Row2Value2,Field3=Row2Value3
Field1=Row3Value1,Field2=Row3Value2,Field3=Row3Value3

Is there an easy way to do this?