Export table to Excel - insert after last record on Excel wksheet

How do I tell Excel or Access to export the data from Access table to insert into an existing Excel wksht after last record.  When I am trying to use the docmd.transferdatabase the options are limited.  Is there a work around for this issue.

Thanks,

Karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hi Karen,

What I would do is populate either an ADO or DAO recordset with the info you need, and then do this:

Dim xlApp As Object, xlWb As Object, xlWs As Object

'insert code to populate a variable, rs, with your recordset

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\folder\subfolder\foo.xls")
Set xlWs = xlWb.Worksheets("Name of Worksheet")

xlWs.[a65536].End(-4162).Offset(1, 0).CopyFromRecordset rs

xlWb.Save
xlWb.Close

Set xlWs = Nothing
Set xlWb = Nothing

xlApp.Quit

Set xlApp = Nothing

Regards,

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
:)
0
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the code, this looks great, however, I have 1 more question.

I need to make this so it creates and new folder name and worksheet name for each month.  The locaton of the folder and wksheet will be static, however the actual name will change each month.  ie Pivot_Mar06, Pivot_Apr06)

How do I modify the above code to allow for the changing name automatically.  I was thinking about setting so variables to pass the current date, pfileName = "Pivot " &"_" & Format(date(), "MMM") &"" & Format(Date(), "YYYY").

thanks,

Karen
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
This is what I have so far, what am I doing wrong . it errors at the "Set RS = CurrentDb.QueryDefs(strSQL)"

I know I am missing something.  

Public Function PivotExport()
Dim xlApp As Object, xlWb As Object, xlWs As Object, nFName As String, strSQL As String
Dim RS As ADODB.Recordset

nFName = "Pivot _" & Format(Date, "MMM") & "" & Format(Date, "YY")

    strSQL = "SELECT SystemDate, Carrier, Shortcode, UniformMsg, CountOfid, uniform_messages.uniform_message AS MessageType, tblDistinictBody_Temp.Status, Tarriff" & _
            " FROM tblDistinictBody_Temp LEFT JOIN uniform_messages ON" & _
                    " tblDistinictBody_Temp.MessageType = uniform_messages.id" & _
            " GROUP BY SystemDate, Carrier, Shortcode, UniformMsg, CountOfid, uniform_messages.uniform_message," & _
            " Status, tblDistinictBody_Temp.Tarriff"
    Set RS = CurrentDb.QueryDefs(strSQL)

'insert code to populate a variable, rs, with your recordset

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("I:\Access Reports\MasterMessageSystem\Pivot Tables\ &""& nfname &" \ "& nfname")
Set xlWs = xlWb.Worksheets("qryDistinctMsg_Export")

xlWs.[a65536].End(-4162).Offset(1, 0).CopyFromRecordset RS

xlWb.Save
xlWb.Close

Set xlWs = Nothing
Set xlWb = Nothing

xlApp.Quit

Set xlApp = Nothing
End Function

thanks,

Karen
0
Karen SchaeferBI ANALYSTAuthor Commented:
I am still looking for a solution.
0
Karen SchaeferBI ANALYSTAuthor Commented:
Public Function PivotExport()
Dim xlApp As Object, xlWb As Object, xlWs As Object, nFName As String, nPath As String
Dim rs As ADODB.Recordset

nFName = Format(Date, "MMMM YY""\Pivot_""MMYY"".xls""")
nPath = "I:\Access Reports\MasterMessageSystem\Pivot Tables\" & nFName

Set rs = New ADODB.Recordset

rs.Open "SELECT Format(SystemDate,'Short Date'), Carrier, Shortcode, UniformMsg, CountOfid, uniform_messages.uniform_message AS MessageType," & _
                " tblDistinctBody_Temp.Status, Tarriff" & _
    " FROM tblDistinctBody_Temp LEFT JOIN uniform_messages ON" & _
                " tblDistinctBody_Temp.MessageType = uniform_messages.id" & _
                " where SystemDate = DateAdd('d',Date(), -1)" & _
    " GROUP BY SystemDate, Carrier, Shortcode, UniformMsg, CountOfid, uniform_messages.uniform_message," & _
                " Status, tblDistinctBody_Temp.Tarriff", CodeProject.Connection, _
    adOpenStatic, adLockReadOnly
With rs

    If Not .EOF Then
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Open(nPath)
        Set xlWs = xlWb.Worksheets("qryDistinctMsg_Export")

        xlWs.[a65536].End(-4162).Offset(1, 0).CopyFromRecordset rs
       
    End If
    xlWb.Save
    xlWb.Close
End With
Set xlWs = Nothing
Set xlWb = Nothing

xlApp.Quit

Set xlApp = Nothing
End Function

this is my final solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.