Export Data to Excel Without Overwriting Existing Data

[DaneS]

Is there a way to do this?

 

[DaneS]

Anyone?.... Bueller?

 

[boblarson]

Is there a way to do this?

Yes, there is. But since you haven't given much info, not much can be given back. What data are you trying to export, how much, where is it supposed to go, etc.

 

[DaneS]

Yes, there is. But since you haven't given much info, not much can be given back. What data are you trying to export, how much, where is it supposed to go, etc.

Ah, sorry! I'm trying to export the results from a crosstab query named "Kronos" to an excel spreadsheet named "Variance". The data is a single row of 6 columns that I want to append to the spreadsheet once a week, which means I want all of the previous data to remain unchanged.

 

[boblarson]

Okay, something like this:

Function SendXTabToExcel()
    Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim rst As DAO.Recordset
    Dim lngRow As Long
    
    Set objXL = CreateObject("Excel.Application")
    
    With objXL
        .Visible = True
        Set xlWB = .Workbooks.Open("filePathAndNameHere")
        
        Set rst = CurrentDb.OpenRecordset("Kronos")
        
        Set xlWS = xlWB.Worksheets("Variance")
        
        lngRow = xlWS.Range("A1").SpecialCells(xlCellTypeLastCell).Row
        xlWS.Cells(lngRow, 1).CopyFromRecordset rst
        
        xlWB.Close True
        .Quit
        
    End With
    Set objXL = Nothing
End Function

 

[DaneS]

Will this work if I want to code it into an On-click button command on one of my forms?

ex. Private Sub Button1_Click()

I'm looking into it now..

 

[boblarson]

Will this work if I want to code it into an On-click button command on one of my forms?

ex. Private Sub Button1_Click()

I'm looking into it now..

Yes, I just put it as a function you would paste into a standard module and then call it in the click event of your button. But you could put it directly in there.

 

[DaneS]

Great, thank you very much

 

[DaneS]

Hmm, I seem to be getting this error when I call the function with a button:


The debugger highlights this part of my code and says xlCellTypeLastCell = Empty

lngRow = xlWS.Range("A1").SpecialCells(xlCellTypeLastCell).Row

 

[boblarson]

Try changing to this:

lngRow = xlWS.Range("A1").SpecialCells(11). Row

 

[DaneS]

Try changing to this:

lngRow = xlWS.Range("A1").SpecialCells(11). Row

It's exporting the data now, but it's not copying the column headers. It also overwrites the first line of existing numbers each time I run the macro.

Edit: I don't want it to copy the column headers. Hah! Half of that problem solved.

 

[DaneS]

Currently trying to figure out how to move the copied cells down one row.

xlWS.Cells(lngRow, 1).CopyFromRecordset rst copies them to the last row of pre-existing data.

 

[boblarson]

Maybe using:

xlWS.Cells(lngRow + 1, 1).CopyFromRecordset rst

 

[DaneS]

That was it. Everything works perfectly. Thank you!

 

[Farahatiqah34]

Okay, something like this:

Function SendXTabToExcel()
    Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim rst As DAO.Recordset
    Dim lngRow As Long
    
    Set objXL = CreateObject("Excel.Application")
    
    With objXL
        .Visible = True
        Set xlWB = .Workbooks.Open("filePathAndNameHere")
        
        Set rst = CurrentDb.OpenRecordset("Kronos")
        
        Set xlWS = xlWB.Worksheets("Variance")
        
        lngRow = xlWS.Range("A1").SpecialCells(xlCellTypeLastCell).Row
        xlWS.Cells(lngRow, 1).CopyFromRecordset rst
        
        xlWB.Close True
        .Quit
        
    End With
    Set objXL = Nothing
End Function

HELP ME PLEASE ! I try use this code to export my query but I get an error that said: Too Few Parameters. Expected 1.

The code I put on the on click function

 

[JHB]

I suppose "Kronos" is a query, correct?
If yes, I think you are missing a criteria.

 

[GinaWhipp]

There are a few lines there that need adjusting. If that is the code you are using then you need to enter your path name and the name of the tab you are exporting to. It's not just copy\paste\and use.

 

[BornOnTheWrongPlanet]

I needed some code to export to a locked statistics workbook and found the original code from boblarson very helpful. I ran into a couple snags because I needed to send to a specific range (which included locked cells that won't overwrite), but did a minor mod to specify the range to start in rather than cell row and column references. Simple call command from a button and bam.



++++++++++++++++++
Public Sub Export_Data(qName As String, wsName As String, wsRange As String)

Dim MySheetPath As String
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst1 As Recordset
Dim lngRow As Long

Set objXL = CreateObject("Excel.Application")
MySheetPath = "SheetPathNameHere"

With objXL
.Visible = True
Set xlWB = .Workbooks.Open(MySheetPath)
Set rst1 = CurrentDb.OpenRecordset(qName)
Set xlWS = xlWB.Worksheets(wsName)

xlWS.Range(wsRange).CopyFromRecordset rst1

xlWB.Close True
.Quit

End With
Set objXL = Nothing

End Sub