Open a csv file in Excel copy and paste that data into an Access Database table

Question

I have the following Outlook VBA that runs when an email comes in that will open a csv file in Excel, copy the data in it (excluding the header row), open an Access Database, open a table, delete the tables rows and paste the new data in and close both Access and Excel when complete.

I have this code running on several rules and keep getting a 91 error code.

Code below:

Public Sub CopyPasteIAFeed(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim ExApp As Excel.Application
Dim ExWbk As Workbook

    On Error GoTo CopyPasteIAFeed_Error

Set ExApp = CreateObject("Excel.Application")
Set ExWbk = ExApp.Workbooks.Open("C:\Users\" & Environ("UserName") & "\Documents\NCR\Data Feeds\Report NCR - Daily New Activity Requests.csv")
' Open Feed in Microsoft Excel window.
ExApp.Visible = True
ExApp.ScreenUpdating = True
ExApp.ActiveSheet.Range("A2").Select
ExApp.ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Dim oApp As Access.Application
Dim LPath As String

LPath = "C:\Users\" & Environ("UserName") & "\Documents\NCR\Database\SP - Link to KM - Non-Critical Request Repository.accdb"
Set oApp = CreateObject("Access.Application")
' Open database in Microsoft Access window.
oApp.OpenCurrentDatabase LPath
oApp.Visible = True
oApp.DoCmd.OpenTable "ReportNCRDailyNewActivity", acViewNormal, acEdit
oApp.DoCmd.RunSQL "DELETE * FROM ReportNCRDailyNewActivity"
oApp.DoCmd.RunCommand acCmdPasteAppend

oApp.CloseCurrentDatabase
oApp.Quit acQuitSaveAll
ExApp.CutCopyMode = False
ExApp.Quit

Set objAtt = Nothing
Set oApp = Nothing
Set ExApp = Nothing

MsgBox "InStream Activity Feed Imported. Continue"

   On Error GoTo 0
   Exit Sub

CopyPasteIAFeed_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CopyPasteIAFeed of Module Module10"

End Sub

It does not say but it would appear that once the Excel workbook is opened and (A2) is selected it stops right there. — Cory West, Oct 30 '15 at 12:23
For clarity here is the error i am getting Error 91 (Object variable or With block variable not set) in Procedure CopyPasteIAFeed of Module Module 10. Am I missing something in my code. This ran once and worked but hasnt since. — Cory West, Oct 30 '15 at 12:41
You are using `ActiveSheet` and `Selection` - very unreliable ways of adressing data, see [this post](http://stackoverflow.com/questions/12426794/select-activesheet-activecell-etc). Does the error still occur if you fix that? — Verzweifler, Oct 30 '15 at 13:06
ExWst.Range("A2").Select ExWst.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ExWst.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy — Cory West, Oct 31 '15 at 13:40
The code works the first time it runs but the second time it throws the error — Cory West, Oct 31 '15 at 14:18

score 1 · Answer 1 · answered Nov 01 '15 at 02:50

No need to even use Excel as MS Access can adequately upload csv files using DoCmd.TransferText.

...
Dim oApp As Access.Application
Dim LPath As String

LPath = "C:\Users\" & Environ("UserName") & "\Documents\NCR\Database\SP - Link to KM - Non-Critical Request Repository.accdb"

Set oApp = CreateObject("Access.Application")
' Open database in Microsoft Access window.
oApp.OpenCurrentDatabase LPath
oApp.DoCmd.RunSQL "DELETE * FROM ReportNCRDailyNewActivity"
oApp.DoCmd.TransferText acImportDelim, , "ReportNCRDailyNewActivity", LPath, True

oApp.CloseCurrentDatabase   
oApp.Quit 
...

Just aside, Access ships by default with the Jet/ACE SQL Engine and can migrate and connect to other Relational Database Management Systems (RDMS). Consider using this robust software as the center point to any data migration needs. As a database client, it can handle various file types: csv, txt, tab, xlsx, sql, xml, even html, and ODBC/OLEDB sources. Hence, your Outlook macro will serve well to be an Access VBA module!

Open a csv file in Excel copy and paste that data into an Access Database table

1 Answers1