Export to Excel, if row is not null, export to next row (1 Viewer)

MushroomKing · Jul 17, 2018

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.

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 · Jul 17, 2018

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'

Code:

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 · Jul 17, 2018

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 · Jul 17, 2018

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 · Jul 17, 2018

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

MushroomKing · Jul 17, 2018

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...

Code:

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 · Jul 17, 2018

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.

Code:

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

Gasman · Jul 17, 2018

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?

MushroomKing said:
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...

Code:

DoCmd.OpenQuery "KPICOLLECTIVE" Call XferData2XL

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

THANKS!!!!!

MushroomKing · Jul 17, 2018

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 · Jul 17, 2018

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 · Jul 17, 2018

Thanks for the reply man!

Well, both are correct.

.ActiveCell.Offset(1, 0).Select

and the correct refference.

Gasman · Jul 17, 2018

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

Code:

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 · Jul 17, 2018

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 · Jul 17, 2018

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

MushroomKing · Jul 17, 2018

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 · Jul 17, 2018

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 · Jul 17, 2018

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

Code:

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

Then change this...

Code:

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

to...

Code:

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...

Code:

Dim strSQL As String

...to the top.

Gasman · Jul 17, 2018

I was about to say the same thing.

Code:

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 · Jul 17, 2018

Great minds think alike!

Gasman · Jul 17, 2018

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

Export to Excel, if row is not null, export to next row (1 Viewer)

Registered User.

Well-known member

Registered User.

Enthusiastic Amateur

Well-known member

Registered User.

Immoderate Moderator

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

AWF VIP

Enthusiastic Amateur

AWF VIP

Enthusiastic Amateur

Similar threads

Users who are viewing this thread