Export to Excel, if row is not null, export to next row

[MushroomKing]

Hi everyone,

I'm exporting some query data to an Excel sheet.

However, i don't want to overwrite the data (on the same row) everytime i run the code.

DoCmd.TransferSpreadsheet acExport, 8, "query", "C;\...", True, "cellrange"

I would rather keep all the data.
So when i run the code, i want to check which next row is empty, and then export.

 

[Ranman256]

then you cannot use: TransferSpreadsheet
which writes from A1.
So you must take control of Excel and paste the data.

you MUST put Excel in the program REFERENCES, in VBE menu (Alt-F11): TOOLS , REFERENCES
checkmark the 'Microsoft Excel x.xx Object library'

sub XferData2XL()
dim sFile as string
Dim xl As Excel.Application
dim rst

sFile = "c:\folder\myfile.xls"
set rst = currentdb.openrecordset("select * from table")
Set xl = CreateObject("excel.application")
With xl
    .Workbooks.Open sFile
    .Range("A1").Select
    .Selection.End(xlDown).Select      'goto bottom of data
    .ActiveCell.Offset(1, 0).Select    'next free row
    .ActiveCell.CopyFromRecordset rst  'paste data

    .ActiveWorkbook.Save
    '.Quit
End With

Set xl = Nothing
Set rst = Nothing
End Sub

 

[MushroomKing]

Whooo! Thanks man! Looks promising. Just tried it.

Gives me "too few parameters, expected 2" on line:

Set rst = CurrentDb.OpenRecordset("select * from KPICOLLECTIVE")

KPICOLLECTIVE is a query that has only 1 row with values.
Does that matter in any way?

Anyway, big step forward. Thanks!

 

[Gasman]

Ranman256 has given you generic code to work with.
It sounds like your query requires two parameters for it to work.


Nothing to do with Excel.

 

[Ranman256]

the sql doesnt seem to have params,
is KPICOLLECTIVE a table? If so, there's nothing there to ask for params.

 

[MushroomKing]

Correct guys! It is asking for 2 parameters and its a query.
It needs a starting and an ending date (between date),
Which it takes directly from the form fields.

So i thought, just run the query and then call the export and it will be solved. But no...

DoCmd.OpenQuery "KPICOLLECTIVE"
Call XferData2XL

How can it get the parameters? I'm running the query, so it should have it already???

THANKS!!!!!

 

[The_Doc_Man]

The missing parameter in the OpenRecordset MIGHT (stress MIGHT) be so simple as to give it an open-mode, since it isn't a table. I would make the 2nd parameter to define a dynaset, which is the most flexible option for your case, I believe.

Set rst = CurrentDb.OpenRecordset("select * from KPICOLLECTIVE", dbOpenDynaset)

 

[Gasman]

If you have hardcoded the parameters as the form controls, the form needs to be open.?
I'd expect you would just use Select * from KPICOLLECTIVE then?

Correct guys! It is asking for 2 parameters and its a query.
It needs a starting and an ending date (between date),
Which it takes directly from the form fields.

So i thought, just run the query and then call the export and it will be solved. But no...

DoCmd.OpenQuery "KPICOLLECTIVE"
Call XferData2XL

How can it get the parameters? I'm running the query, so it should have it already???

THANKS!!!!!

 

[MushroomKing]

Thanks for that addition!

Now, with or without it, i get another error :banghead::banghead:


Application defined or object defined error

ActiveCell.Offset <<<debugger


Life's hard...coding is harder

 

[Gasman]

It's .ActiveCell.Offset
All the statements in the With loop are prefixed by a period .

And you must include the reference that Ranman256 stated in his first post?

 

[MushroomKing]

Thanks for the reply man!

Well, both are correct.

.ActiveCell.Offset(1, 0).Select

and the correct refference.

 

[Gasman]

Yeh, I had a problem when I tried to use it.

I use the method below, to get last used row. Also if the sheet starts empty the address was 65536, so make sure there are titles or something in row 1 at least and make sure you select a column that will always have data.


I've tested this on my Table1 and it works, the rest is up to you.

HTH

Sub XferData2XL()
Dim sFile As String
Dim xl As Excel.Application
Dim rst
Dim lngLast As Long

sFile = "c:\temp\test.xls"
Set rst = CurrentDb.OpenRecordset("select * from table1")
Set xl = CreateObject("excel.application")
With xl
    .Workbooks.Open sFile
    .Range("A1").Select
    '.Selection.End(xlDown).Select      'goto bottom of data
    lngLast = .Range("A" & Rows.Count).End(xlUp).Row
    If lngLast = 65536 Then
        MsgBox "Sheet is full"
        GoTo ExitSub
    End If
    Range("A" & lngLast + 1).Select
    '.ActiveCell.Offset(1, 0).Select    'next free row
    .ActiveCell.CopyFromRecordset rst  'paste data

    .ActiveWorkbook.Save
    .Quit
End With

ExitSub:
Set xl = Nothing
Set rst = Nothing
End Sub

 

[MushroomKing]

Thank god for you people! Cant say how much i appriciate the help.

It works, but if i put some parameter stuff in there it complains again.

For example, in 1 of the queries, i have the following field:

Date: [Forms]![stats_form]![startdate]

This works fine when i use it elsewhere, just not with the code above.
I'm so pissed because it don't make sense to me.
It does run the query and writes the date! Why wouldnt it run that statement?

Sigh, almost there though. Thanks guys!!!!!

 

[Gasman]

The form will have to be open for that syntax to work, as I mentioned before.

 

[MushroomKing]

Right but it is open. The button that runs the code is on that same form.
It just doesn't seem to do anything with it.

SELECT * FROM KPICOLLECTIVE

That part looks like its made for a table, not for a query that has to run itself first.

I run the query in VBA first en then the rest of the code, but this doesn't help either

 

[Gasman]

If you select from the query called KPICOLLECTIVE and that has the parameters and the form is open, I would have expected it to work?

 

[GinaWhipp]

Since the query has parameters you're going to have set the recordset another way. You need to *spell out* the query, i.e.

strSQL = "Your Query Here spelled out, not the name of the Query
                            "WHERE FieldFromQuery =" & Me.startdate & ""

Then change this...

Set rst = CurrentDb.OpenRecordset("select * from table1")

to...

Set rst = CurrentDb.OpenRecordset(strSQL)

This is because the parameter must be *set* before the query is actually *run* which is irrelevant to the Form being open even if the controls have values. Note, you need to put the strSQL line under the sFile line and don't forget to add...

Dim strSQL As String

...to the top.

 

[Gasman]

I was about to say the same thing.

strSQL = "SELECT Table1.* From Table1 WHERE Table1.CreatedBy = '" & Me.Text4 & "'"
sFile = "c:\temp\test.xls"
Set rst = CurrentDb.OpenRecordset(strSQL)

Sorry for leading you astray.

 

[GinaWhipp]

Great minds think alike!

 

[Gasman]

Well I am still thinking the date has to be in the format #mm/dd/yyyy# ?