Export Query to Excel Keeping Formatting

[Antanas_07]

Hi Dx,
I run out of ideas. The SQL statement OK. it works: On Click Event, after breaking point- Debug.Print, Copy to SQL query window, run, and I see the record. But when it comes to Function: nothing happens after click it is just like in your comment:
No Records - No Report a.k.a. no shoes, no shirt, no service. I used other sources how to open Recordset, but the result the same. This is my last "creation":
Public Function MonthlyReport()
Dim dbCustomerComplains_DB_Unlocked As DAO.Database
Dim rstSQLMonthlyRpt As DAO.Recordset
Dim strSQLMonthlyRpt As String
Dim WorkSheetName As String
Dim intHeaderColCount As Integer
Dim intMaxheaderColCount As Integer
Dim intMaxRecordCount As Integer
Dim intWorksheetNum As Integer
Dim intRowPos As Integer
Dim ObjXL As Object

Set dbCustomerComplains_DB_Unlocked = CurrentDb

strSQLMonthlyRpt = "SELECT * FROM tblCCQualityMain WHERE ComplaintOpenDate = #30/05/2013#; "
Set rstSQLMonthlyRpt = dbCustomerComplains_DB_Unlocked.OpenRecordset(strSQLMonthlyRpt, dbOpenSnapshot)
End Function
Everything simplified up to Primary School level.
I Give up.

 

[Rx_]

On the very top of your code module - add
Option Explicit

In Module - menu choose Debug then Compile
Did it the Compile all check out OK?

Lets make it more simple! See if it works.

On the very top of your code module - add
Option Explicit

on error resume next
Debug.Print "The Error is " & and Err.Description
strSQLMonthlyRpt = "SELECT * FROM tblCCQualityMain"
Debug.Print "The Error is " & and Err.Description
Debug.Print " strSQLMonthlyRpt"
Set rstSQLMonthlyRpt = CurrentDb.OpenRecordset(strSQLMonthlyRpt, 2, dbSeeChanges)
Debug.Print "The Error is " & and Err.Description
debug.Print "record count is " & rstSQLMonthlyRpt.recordcount

 

[Antanas_07]

Dear Dx,
I did not take a holiday and was working hard on the subject when I had time. Now the record in Immediate window is:
The Error is
The Error is
record count is0
From what I understand, there is something with recordset which I am not able to set open. I went to numerous sources fell assleep several times. I did write a bit of code just to open Excel table. When I look to query or query - recordset. it opens the excel sheet with column names. in SQL case the result emty datasheet. So to narrow search I think I have to concentrate how to Open Recordset from SQL. PS: it is allways Option Explicit in my code.
Hope you stil with me

 

[Rx_]

No errors, that is good. The Record count is zero - your query is returning zero records.
Debug.Print "The Error is " & and Err.Description
strSQLMonthlyRpt = "SELECT * FROM tblCCQualityMain"
Debug.Print "The Error is " & and Err.Description
Debug.Print " strSQLMonthlyRpt"
Set rstSQLMonthlyRpt = CurrentDb.OpenRecordset(strSQLMonthlyRpt, 2, dbSeeChanges)
rstSQLMonthlyRpt.MoveLast
Debug.Print "The Error is " & and Err.Description
debug.Print "record count is " & rstSQLMonthlyRpt.recordcount

Note: added a movelast for the recordset.
By chance, did you still include the Date criteria in the SQL statement?
We want to remove that until we get some records to show up. If the date criteria has no records (or has a format problem) then it might explain why there are no records returned. Did you start by selecting all of the records from the table?

 

[Antanas_07]

Hi Rx
I did not include anything. SQL as simple as it is. There are so many questions. First The SQL statement to me missing something. If you try to Run Query with statement "FROM tblCCQualityMain" the error would be: "There has to be at least one parameter selected". So for the sake of parameter I selected field "CCNo". Now SQL statement looks:
strSQLMonthlyRpt = "SELECT tblCCMain.CCNo FROM tblCCMain;"
Then I run the code you had corrected with
rstSQLMonthlyRpt.MoveLast
In the Immediate window I've got the:
The Error is
The Error is
The Error is
strSQLMonthlyRpt
The Error is
record count is 7
Which I would think a good result as in the table there are 7 records! Does this mean you finaly got the Recordset Open?

 

[Rx_]

YES! Now you have 7 records in the recordset (during the run-time, it goes away when the End Sub / End Function is reached).

I have an afternoon of meetings and code development deadlines.
But, now, you can push those 7 records to Excel.

Looks like the SQL statement will need to be revisited later.

 

[Antanas_07]

Hi Dx,
It appiers that now with a bit MoveLast even without selection of the table column [CCNo]
strSQLMonthlyRpt = "SELECT * FROM tblCCMain;"
caunts all 7 records. When that bit removed it counts 1. And I tryed to merge results with excel. No luck. I guess I will wait when you got a minute. If not too much to ask.

 

[Rx_]

You probably have enough post to add an attachment?
Do this if you can't sent the entire database:
Create a new blank Access DB
Open your development side-by-side
Copy and paste the table with the 7 records into the blank one (object bar)
Copy and paste anything with your code.

I can probably spot it faster that way and send it back to you.

 

[Antanas_07]

I would be more then happy to send a database, but Do not know how to make an attachment to my post

 

[Rx_]

On response - click the Go Advanced
It brings up more menu items.
See the paperclip next to the smily option midtop center

For attached tables:
It is possible to do a Top 100 Select Query - change it to MakeTable Query
Then save the top 1000 records in a local table.
You might want to put a filter in the query so the tables will have matching data when they are joined.

Were you able to use the Debug (immediate window) to run your code?

 

[Antanas_07]

Thanks for response, The project suppose to be launched this week, last week was symbolic presentation. So it is no data on the table and I use Test database with symbolic data. Literately I do noit understand why it is not working. All books and web say it does. there is an example in the TestDB with query which is working, but with SQL....:banghead: