none
Using VBA to export data from Access to an Excel template

    Question

  • What I'm trying to do is export data from my tables in Access to an Excel template, using VBA in the Modules section of Access.  I've done this in the past with a different Access database, and I've recycled the same code (I just changed the filenames and filepaths etc) but for some reason it isn't working.

     

    When I try to run the module, the compiler gets stuck and brings up an error message saying "Compile Error: Invalid use of New keyword". It is failing at the line "Dim rs As New Recordset".  However, I've used this line in the past without any problems, so I really don't understand why it isn't working this time.

     

    The code is supposed to copy the data from the specified Access table, open a pre-existing Excel template from the specified directory path, and paste the data into the Excel template and save it on the network drive with the filename being the previous month's name (so as not to overwrite the original template).

     

    This is the code:

     

    Code Snippet

    Option Compare Database

    Public Sub ExportCCValue()

    Dim rs As New Recordset
    Dim C As Connection
    Set C = CurrentProject.Connection
    rs.Open "select * from [001 CC Value Output Table]", C

    Dim x As New Excel.Application
    Dim w As Workbook
    Dim s As Worksheet
    Dim r As Range
    Dim d As String

    d = "really long directory path is here"

    Set w = workbooks.Open(d & "REPORT TEMPLATES\Credit Controller Summary Template.xls")

    Set s = w.Sheets("Value")
    Set r = s.Range("A2")

    r.CopyFromRecordset rs

    s.Columns("A:S").EntireColumn.AutoFit
    s.Columns("A:S").Font.Size = 10

    rs.Close
    Set rs = Nothing
    w.SaveAs d & "-" & PrevMonth(Date), , , , False
    w.Close
    x.Quit

    Set r = Nothing
    Set s = Nothing
    Set w = Nothing
    Set x = Nothing

    End Sub

     

    Public Function PrevMonth(d)
    'Requires the D in brackets, as used in the formulas below

    Dim M
    M = Month(d)
    Select Case M
        Case 2 To 12
                PrevMonth = Format(DateSerial(Year(d), M, 1), "mmm") & "_" & Year(d)
            ' Date Serial, Year, Month (M), Day of Month (1) - Needs name of function
        Case 1
                PrevMonth = "Dec" & "_" & Year(d) - 1
            ' Needs name of Function
    End Select
    End Function

     

     

    I'm using Access 2002, although the file is in Access 2000 format.

     

    Can anyone please tell me what is causing this error and let me know how I can fix it?  Thank you.

     

    Wednesday, April 09, 2008 1:43 PM

Answers

  • Thank you, but I managed to fix it in the end.  It turned out that the problem was due to reference libraries.  I went to Tools then References in the Visual Basic Editor, and added Microsoft ActiveX Data Objects 2.1 Library and Microsoft Excel 10.0 Object Library.  After doing this, the VBA ran successfully.

     

    I didn't realise that you have to set up the references in each individual Access database; I thought that once you had altered the settings within one database, they would automatically carry through to all the databases you set up in the future.  At least I know now.

     

    Wednesday, April 09, 2008 3:19 PM

All replies

  • I can't promise this will work but try:

     

    Code Snippet

    Dim rs As New ADODB.Recordset

     

     

     

    actually, this worked on my end

    Wednesday, April 09, 2008 2:31 PM
  • Thank you, but I managed to fix it in the end.  It turned out that the problem was due to reference libraries.  I went to Tools then References in the Visual Basic Editor, and added Microsoft ActiveX Data Objects 2.1 Library and Microsoft Excel 10.0 Object Library.  After doing this, the VBA ran successfully.

     

    I didn't realise that you have to set up the references in each individual Access database; I thought that once you had altered the settings within one database, they would automatically carry through to all the databases you set up in the future.  At least I know now.

     

    Wednesday, April 09, 2008 3:19 PM
  • Hi Lemon twist,

    I am also facing the same problem when i use the codes mentioned above for transfer the data into excel template. Can you please help me on it. I am getting the same compile error of invalid use of new Keyword.


    VinWin06

    Monday, October 29, 2012 6:53 AM