Export pivot

[sven2]

Hello,

I have made a access application that has a OWC 10 pivottable control on it. I use the function pivottable.export("") to export the pivot table to excel.

When I do this I always get an error in Excel. When I take a look to the logfile ... the information about this error is missing code, xml aborted ???

What can be the reason of this error?

Do I have to insert the OWC10.dll file also in Excel? I haven't done this because this file is not in the list to choose from in Excel?

Thanks in advance,
Sven.

 

[namliam]

I have no clue as to this OWC10.dll but why not simply use the transferspreadsheet command?

 

[sven2]

Hello,

because I want to keep it a pivot (draaitabel).

Best regards,
Sven.

 

[namliam]

Yes with the Transferspreadsheet command you do lose some functionality of the Pivot...

Tho the values for the pivot are there and valid...

 

[sven2]

Hello,

if I try the Transferspreadsheet command I get an error.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQL, strPath

Is it not allowed to use strSQL in this context?

Sven.

 

[namliam]

No you cannot put SQL into the transferspreadsheet command

You have to export a query... What I allways do is, create a dummy query qryExport or something, then:
Currentdb.Querydefs("qryExport").sql = strSQL
Transfer.... , "qryExport"

 

[sven2]

Hello,

seems simple but ...

I created a dummy query ... with the name Q_exporteren

Then i changed the code like:

CurrentProject.connection.Querydefs("Q_exporteren").Sql = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_exporteren", strPath

Then I get an error wrong type ...

What am I doing wrong?

Best regards,
Sven.

 

[namliam]

I think that this
CurrentProject.connection.Querydefs("Q_exporteren" ).Sql = strSQL
should read
Currentdb.Querydefs("Q_exporteren" ).Sql = strSQL

Where are you at in the NL?

 

[sven2]

Hello,

also the currentdb doesn't work ... object "with" isn't defined.
Keep in mind that I am working with SQL tables as backend ...

Transfer to a spreadsheet seem very hard to accomplish ...

Best regards,
Sven.

P.S. now the real disappointment ... I am from Belgium

 

[namliam]

P.S. now the real disappointment ... I am from Belgium

Oh well that explains a lot

Uhm the Currentdb is a default part of access VBA.

You are running this in access right???

Where are you using with??

Currentdb.Querydefs("").sql should be no problem in Access???

 

[sven2]

Hello,

indeed I amworking wth Access but when i do it like you wrote
I get an error 91 object variable not set.

When I compile there is no problem, error 91 appears when I run the code.

This is the complete code:

Private Sub cmdexporteren_Click()

Dim strName As String
Dim strPath As String
Dim strSQL As String


strSQL = " SELECT TblMachine.Machinenaam, TblMoederrollen.Datum, TblMoederrollen.Moederrolnummer, TblPapiersoorten.Papiersoort, TblPapiersoorten.Gramgewicht, TblKwaliteitgegevens.Kwaliteitsnaam, TblKwaliteitswaarde.Kwaliteitswaarde, TblMoederrolOpmerking.Opmerking, TblKwaliteitgegevens.Rapportering " & _
" FROM TblKwaliteitswaarde INNER JOIN TblMoederrollen ON TblKwaliteitswaarde.Moederrolnummer = TblMoederrollen.Moederrolnummer LEFT OUTER JOIN TblMoederrolOpmerking ON TblMoederrollen.Moederrolnummer = TblMoederrolOpmerking.Moederrolnummer INNER JOIN TblPapiersoorten ON TblMoederrollen.PapiersoortID = TblPapiersoorten.PapiersoortID INNER JOIN TblKwaliteitgegevens ON TblKwaliteitswaarde.KwaliteitID = TblKwaliteitgegevens.KwaliteitID INNER JOIN TblMachine ON TblMoederrollen.MachineID = TblMachine.MachineID " & _
" WHERE (TblKwaliteitgegevens.Rapportering = 1) AND (TblMachine.machinenaam = '" & [Forms]![FrmRapportering]![cbomachine] & "') AND (TblMoederrollen.Datum BETWEEN '" & [Forms]![FrmRapportering]![txtbegindatum2] & "' AND '" & [Forms]![FrmRapportering]![txteinddatum2] & "') " & _
" ORDER BY TblMoederrollen.Moederrolnummer "

strName = "Parameters " & Format(Date, "dd-mm-yyyy") & ".xls"
strPath = Application.CurrentProject.Path & "\" & strName

CurrentDb.Querydefs("Q_Temp").Sql = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_Temp", strPath

Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook
Dim xlsSheet As Excel.Worksheet

Set xlsApp = CreateObject("Excel.Application")
Set xlswkb = xlsApp.Workbooks.Add(strPath)

xlsApp.Visible = True

Set xlsSheet = xlsApp.ActiveWorkbook.Worksheets(1)
With xlsSheet
.PageSetup.Orientation = xlLandscape
.Range("A1:Z1").Font.Bold = True
.Range("A1:Z1").WrapText = True
.Range("A1:Z1").Orientation = 90
.Range("A1:Z1").ColumnWidth = 12
End With

xlsApp.Quit

Set xlsApp = Nothing
Set xlswkb = Nothing

End Sub

Thanks,
Sven.

 

[namliam]

strPath = Application.CurrentProject.Path & "\" & strName

That looks more like it would be VB instead of Access....

Tips:
- If you are posting code use the code tags... this means putting {code} infront of it and {/code} behind it. Just interchange {} with [] to get it working
- Make your code readable!
The SQL is just unacceptable...
Try doing something like:
strSQL = " SELECT "
strSQL = strSQL & " TblMachine.Machinenaam, "
strSQL = strSQL & " TblMoederrollen.Datum, "
strSQL = strSQL & " TblMoederrollen.Moederrolnummer, "
strSQL = strSQL & " TblPapiersoorten.Papiersoort, "

It is a little more work, but one day you will be very happy you do it that way

About the Currentdb... I have no idea why that doesnt work... It should just work, no problems...
Make sure your query Q_Temp excists tho.... that would be the only thing I can think of...