Solved Excel xls-Export (1 Viewer)

[wanzi]

I prepare the data using a report (with the appropriate WhereCondition), which I can then export either as a PDF or Excel.xls. I just had to change the acFormat (acFormatPDF or acFormatXLS). In the Excel file the fonts are displayed quite gray. Is there an explanation for this?

Sub
DoCmd.OpenReport strReport, acViewPreview, , strWhere, acHidden, strBez
'Ausgabe PDF
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strDatei
'Ausgabe Excel xls
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strDatei
End Sub

I have now switched the sub to export with TransferSpreadsheet.

Sub
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qryDatei, "C:\test.xlsx", True
End Sub

With this I can create an xlsx Excel file that is displayed perfectly with headings, but a "Report" and a "Where condition" are not possible.
What options do I have?
Thanks for your help!

 

[theDBguy]

What is your WHERE condition? Can you transfer it to qryDatei? What is the SQL statement for that query?

 

[Pat Hartman]

In the Excel file the fonts are displayed quite gray. Is there an explanation for this?

Ask MS. They LOVE faint gray text. Humor aside, try changing your Office Theme

 

[wanzi]

What is your WHERE condition? Can you transfer it to qryDatei? What is the SQL statement for that query?

I have a general query, which is then output to the individual reports with the where condition.

my query:
SELECT AgeAktuell(tblMitglieder.Geburtsdatum,tblMitglieder.Austritt) AS AktAlter, AgeLJahr(tblMitglieder.Geburtsdatum,tblMitglieder.Austritt) AS LJAlter, AgeFJahr(tblMitglieder.Geburtsdatum,tblMitglieder.Austritt) AS FJAlter, MitJahr(tblMitglieder.Eintritt,tblMitglieder.Austritt) AS MJahre, strMMDD(tblMitglieder.Geburtsdatum) AS MMDD, strDDMonat(tblMitglieder.Geburtsdatum) AS DDMonat, Nz(tblMitglieder.Nachname,"")+" "+Nz(tblMitglieder.Vorname,"")+", "+Nz(tblMitglieder.strasse,"")+", "+Nz(tblMitglieder.[PLZ],"")+" "+Nz(tblMitglieder.[Ort],"") AS Anschrift, NZ(tblMitglieder.Nachname,"")+" "+NZ(tblMitglieder.Vorname,"") AS NachVorname, tblMitglieder.PLZ+" "+tblMitglieder.Ort AS PLZOrt, *
FROM tblMitglieder;

where condition: z.B.
strWhere = "year(qryMitglieder.Eintritt) = " & Me.cboJahrEin.value

 

[theDBguy]

I have a general query, which is then output to the individual reports with the where condition.

my query:
SELECT AgeAktuell(tblMitglieder.Geburtsdatum,tblMitglieder.Austritt) AS AktAlter, AgeLJahr(tblMitglieder.Geburtsdatum,tblMitglieder.Austritt) AS LJAlter, AgeFJahr(tblMitglieder.Geburtsdatum,tblMitglieder.Austritt) AS FJAlter, MitJahr(tblMitglieder.Eintritt,tblMitglieder.Austritt) AS MJahre, strMMDD(tblMitglieder.Geburtsdatum) AS MMDD, strDDMonat(tblMitglieder.Geburtsdatum) AS DDMonat, Nz(tblMitglieder.Nachname,"")+" "+Nz(tblMitglieder.Vorname,"")+", "+Nz(tblMitglieder.strasse,"")+", "+Nz(tblMitglieder.[PLZ],"")+" "+Nz(tblMitglieder.[Ort],"") AS Anschrift, NZ(tblMitglieder.Nachname,"")+" "+NZ(tblMitglieder.Vorname,"") AS NachVorname, tblMitglieder.PLZ+" "+tblMitglieder.Ort AS PLZOrt, *
FROM tblMitglieder;

where condition: z.B.
strWhere = "year(qryMitglieder.Eintritt) = " & Me.cboJahrEin.value

You could try adding the Where Clause in your query like:

SELECT...
FROM tblMitglieder
WHERE Year(Eintritt)=Forms!FormName.cboJahrEin

Just a thought...

 

[Uncle Gizmo]

You could make your SQL statement much simpler and easier to read by taking out the tables.

Take out the Tables​

You can remove most of the references to the table name when your SQL Statement is based on a single table. This trick depends on NOT just selecting the table name, but also the DOT “.” following it. You need to select the dot (.) to prevent your action removing the actual reference to the table in the FROM Clause.

See this video at time index 1.40

 

[wanzi]

Found a solution. With a small adaptation to my existing SUB, the problem with the gray data display in Excel.xls was solved.
Everything is nice and black!

Sub
'Ausgabe PDF
DoCmd.OpenReport strReport, acViewPreview, , strWhere, acHidden, strBez
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "C:\temp\test.pdf"

'Ausgabe Excel xls
DoCmd.OpenReport strReport, acViewPreview, , strWhere, acHidden, strBez
DoCmd.OutputTo acOutputReport, strReport, acFormatXLSX, "C:\temp\test.xls"
End Sub

Anyway, thanks for your information.