Exporting a Query to a Spreadsheet

[grovelli]

Thank you Hans, here's the link to Stat.zip which contains both the Excel file and the mdb

[HansV]

Thanks, but that won't work because the query refers to linked tables.

[grovelli]

The content of the qtxx query gets correctly transferred to the qt sheet in Stat.xlsx. The problem is that, only with Access 2013, the ProdvsBDG-mese sheet displays the correct values only upon recalculation.

[HansV]

The code that I posted earlier correctly updates the values on the ProdvsBDG-mese sheet for me. I cannot test the complete code since the data in your database reside in SQL Server, not in the database itself.

[grovelli]

Right-clicking a linked table in Access 2013 gives you the handy(new to me at least) command Convert to Local Table so I did that but I don't understand why, when you close the database and reopen it, the tables are back to being linked so I tried to do the usual copy and paste of the tables without success so I solved it by converting the mdb to accdb and then using Convert to Local Table. Here's the complete database and the behaviour I get is the same even with local tables.

[HansV]

I still get this.

S0491.png

Please provide a WORKING database.

[grovelli]

Don't know why you get that message since there aren't linked tables anymore but in any case it should clear once you selec ODBC Origin from the Control Panel and add Italgen Project as depicted here

DSN.jpg

[HansV]

There are no linked tables anymore, but the database still contains pass-through queries, and the query that you export, qtx, depends on those...

[grovelli]

It doesn't seem to be straightforward to revert pass-throughs to normal select queries but I'll try to do it.

[grovelli]

Here's the accdb with the converted query, just press the Previous Month button on the startup form; you'll see(if you're using Access 2013) that the ProdvsBDG-mese sheet in Stat.xlsx is still beset by the same problem.

[HansV]

Thanks. I downloaded the new database and changed the path to stat.xlsx in the code. I used the workbook from the zip file that you made available earlier this week.

Before clicking the button, column A of the ProdvsBDG-mese contained incorrect dates. But when I click the button, I get the correct result:

S0502.png

Using Office 2013, fully updated.

[grovelli]

Where do you see the build in Access 2013? I'd like to see if it matches yours.

[HansV]

By the way, the On Click event procedure of the command button does not contain the code that I suggested. Try changing the line

Code: Select all

   xlBook.Sheets("ProdvsBDG-mese").Select

to

Code: Select all

    Set xlSheet = xlBook.Sheets("ProdvsBDG-mese")
    xlSheet.Select
    Dim cel As Range
    For Each cel In xlSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        cel.Formula = cel.Formula
    Next cel

[HansV]

To see the build of Access 2013, select File > Account.

S0503.png

Click "About Access" on the right hand side.
The build is shpwn in the "About Microsoft Access" dialog:

S0504.png

[grovelli]

And once again your last piece of code saved the day
Thank you so much Hans
Does your version produce the correct results even without the added code that recalculates the ProdvsBDG-mese sheet?

[HansV]

Yes, the screenshot that I posted was produced without adding the extra code.

[grovelli]

Then I guess it's a build issue. Here's mine, what's yours?

[HansV]

See above! Mine looks slightly newer.

[grovelli]

Oopsie, missed that one
While waiting to upgrade, I've tried to solve it by converting RTEPSpecial.mdb to accdb format but when I open the accdb, code stops at
Set rs = db.OpenRecordset("tblReconnectODBC", dbOpenSnapshot)
in Function fReconnectODBC() As Boolean
with Error #13: Type Mismatch
fReconnectODBC is run in the AutoExec macro, I deleted the macro from the RTEPSpecial - Copia (2).accdb file I put on OneDrive since there were no more linked tables.

[HansV]

The order of the references in Tools > References... changed, and now the ADO reference is listed above the DAO reference. So the line

Dim ..., rs As Recordset

declares rs as an ADO recordset. To avoid this problem, use

Dim ..., rs As DAO.Recordset

You should always declare a recordset explicitly as DAO.Recordset or as ADODB.Recordset, depending on which one you want to use. This avoids any possible confusion over the data type.