Export queries to excel with Query name as the header

[Summer123]

Hello, is there anyway to print the query name as the header on the first row? For example i have 50+ queries that i am exportin currently to one spreadsheet with different tabs for each query, i am also formatting each tab, however the query name is printed as the tab name on each tab but gets cut off as some of the names are too long. is there a way to print the query name on each tab as the header row is the first row so the user knows which query he has run? I can post the code I have so far if needed

 

[Summer123]

does anyone know of how to do this? is it even possible?

 

[JohnLee]

Hi,

There is a limit to the number of characters you can have in the tab of an excel spreadsheet, so if your query names always exceed that number of characters, you will always have this problem.

I would advise revising the length of the names of each of your queries.

John

 

[JohnLee]

Hi,

This is code I have for exporting data to an excel spreadsheet naming the spreadsheet and the worksheet tab name:

[COLOR=blue]Dim[/COLOR] ExcelFile [COLOR=blue]As[/COLOR] String                 
[COLOR=blue]Dim[/COLOR] ExcelWorksheet [COLOR=blue]As[/COLOR] String       
[COLOR=blue]Dim[/COLOR] Ques [COLOR=blue]As[/COLOR] String                      
[COLOR=blue]Dim[/COLOR] QueryName [COLOR=blue]As[/COLOR] String                
[COLOR=blue]Dim[/COLOR] objDB [COLOR=blue]As[/COLOR] Database                   
[COLOR=blue]Dim[/COLOR] MyDate
 
MyDate = Now()                           
 
    ExcelFile = "M:\Customer Satisfaction\Sky Repair Plan\SkySpreadsheets\Sky" & "_" & Format(MyDate, "ddmmyy") & ".xls"
    ExcelWorksheet = "Sky " & Format(MyDate, "ddmmyy")    
    Ques = "G:\eFlowStatsFrontEnd.mdb"
    QueryName = "qrySkyQtrAnalysis"                     
 
    [COLOR=blue]Set[/COLOR] objDB = OpenDatabase(Ques)

I've not tried this but you could assign the "QueryName" to the "ExcelWorksheet" and this might achieve what your looking for, but remember there is a limit to the number of characters you can have in the tab of a worksheet.

Hope this helps.

John

 

[Summer123]

thank you both!!
this does help however i was looking for a way to have the name of the query in the spreadsheet itself in the first row is there a way to do that??

so for example

sheet1 - queryone and tab is named queryone (already working fine) but i want it so the word "queryone" is in the first row of that tab, right before the results are displayed
sheet2 - querytwo and tab is named querytwo (already working fine) but i want it so the word "querytwo" is in the first row of that tab again right before the results are displayed

anyway to do that??? is that not even possible?

thank you both again

 

[boblarson]

It is totally possible using Excel Automation code:

Dim objXL As Object
Dim rst As DAO.Recordset
Dim strQueryName As String
Dim fld As DAO.Field
 
strQueryName = "tableOrQueryYouWantToExport"
 
Set rst = CurrentDb.OpenRecordset(strQueryName)
 
Set objXL = CreateObject("Excel.Application")
 
[FONT=Courier New]   For Each fld In rst.Fields[/FONT]
[FONT=Courier New]       objXL.ActiveCell = fld.Name[/FONT]
[FONT=Courier New]       objXL.ActiveCell.Offset(0, 1).Select[/FONT]
[FONT=Courier New]   Next[/FONT]
[FONT=courier new]   rst.MoveFirst[/FONT]
 
 
With objXL
    .Activesheet.Range("A1").CopyFromRecordset rst
    .Activesheet.Range("A1").Select
    .Selection.EntireRow.Insert
    [B][COLOR=red].Activecell.Value = strQueryName[/COLOR][/B]
End With
 
rst.Close
 
Set rst = Nothing
objXL.UserControl = True
Set objXL = Nothing

Just a bit of a sample thrown together there.

 

[JohnLee]

Hi,

This is a snippit of code that I have for my export to excel, the code forces a insert first row, and then inserts the information I want in the first cell of the first column. It should give you something to work with:

[COLOR=blue]Set[/COLOR] objsheet = ObjExcel.ActiveWorkbook.Worksheets(1) [COLOR=green]'Set the objsheet to active worksheet in the active workbook[/COLOR]
    [COLOR=blue]With[/COLOR] objsheet  [COLOR=green]'With the active worksheet
[/COLOR]        .Rows("1:1").Font.Bold = [COLOR=blue]True[/COLOR] [COLOR=green]'Set the first row font to bold
[/COLOR]        .Rows("1:1").Font.Underline = xlUnderlineStyleSingle    [COLOR=green]'U[/COLOR][COLOR=green]nderline [/COLOR][COLOR=green]the text in each cell of the first row
[/COLOR]        .Rows("1:1").Select  [COLOR=green]'Insert a new row
[/COLOR]        ObjExcel.Selection.Insert Shift:=xlDown
            ObjExcel.Range("A1").Select [COLOR=green]'Select Cell A1
[/COLOR]            ObjExcel.ActiveCell.FormulaR1C1 = "[COLOR=red]Sky[/COLOR]"  [COLOR=green]'Assign the text "Sky [COLOR=red]This is where your query name would go[/COLOR]" to this cell
[/COLOR]            ObjExcel.Range("B1").Select  [COLOR=green]'Select Cell B1
[/COLOR]            ObjExcel.ActiveCell.FormulaR1C1 = "As At Date :"    [COLOR=green]'Assign [/COLOR][COLOR=green]the text "Date:" to this cell
[/COLOR]            ObjExcel.Range("D1").Select [COLOR=green]'Select Cell D1
[/COLOR]        ObjExcel.Selection.NumberFormat = "@"
        [COLOR=blue]With[/COLOR] ObjExcel.Selection
            .HorizontalAlignment = xlLeft [COLOR=green]'Left align the text
[/COLOR]            .VerticalAlignment = xlBottom [COLOR=green]'Bottom align the text
[/COLOR]            .ReadingOrder = xlContext
        [COLOR=blue]End With[/COLOR]

I hope this helps.

John

 

[Summer123]

thank you Bob and John!
Let me get to work with the examples you have given and see waht happens... thank you both again!!!

 

[Summer123]

ok so i tried using what Bob had given and below in red is where i got the error and also i have another question in the code it self...

For Each ctl In MyForm.Controls
Select Case ctl.ControlType
Case acCheckBox
If ctl = True Then
Set rst = db.OpenRecordset(ctl.Name)
If Not rst.EOF Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
End If
rst.Close
Set rst = Nothing
End If
End Select
Next ctl
strFileName = Dir("C:\Errors.xls")

If strFileName = "Errors.xls" Then
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(fileIn)
intCountofSheets = xlBook.sheets.Count
intCurrentSheet = 1
Do While intCurrentSheet <= intCountofSheets
xlBook.Worksheets(intCurrentSheet).Activate
With xlApp.ActiveSheet.UsedRange
.Font.Name = "Tahoma"
.Font.Size = 10
.rows.Select
.rows(1).Font.Bold = True
.rows(1).Font.Size = 10
.rows(1).Font.Name = "Tahoma"
.rows(1).Interior.ColorIndex = 36
.Cells.Select
.Cells.EntireColumn.AutoFit
.Cells.EntireRow.AutoFit
.Range("A1").Select 'Selects first cell to deselect the rest of the sheet.
.Selection.EntireRow.Insert 'error -
"run time error 438 object doesnt support this property or method "

.Activecell.Value = ctl.Name ' also can i use this? as I have multiple queries and can not name each one?
End With

thank you once again both bob and john!

 

[boblarson]

The first red error is because of this:

With xlApp.ActiveSheet.UsedRange


.Selection only works with xlApp

So you would need to just change the line to this:

xlApp.Selection.EntireRow.Insert

 

[Summer123]

thanks Bob... that worked but now getting an error on ctl.name.. i assume i cant use that but in order for me to not spell out every query dont i have to use that? am i coding it wrong?

 

[boblarson]

You are iterating through every control up top and once you're done with it all the ctl becomes invalid again. So, which control do you want the name of? How do you know which one it would be. If you know, use that specific control instead.

Plus it looks like you are going through each checkbox and doing something but it won't work for what you want now. But you should be able to use the worksheet name:

.Activecell.Value = .Activesheet.Name

 

[Summer123]

is there some sort of hierarchy i need to consider before i use this code of line? because if i try to insert the .activecell line in it gives me the same run time error. i tried to do several different things by putting the line in different places but same issue...

 

[boblarson]

Oops, yes you also need to do the same:

apXL.Activecell.Value = apXL.Activesheet.Name

 

[Summer123]

i think you mean xlApp.Activecell.Value = xlApp.Activesheet.Name
that worked but the reason for me to print this is because some of the query names are long... and so i wanted to print it in the first row since it was getting cut of in the tab name. By doing the activesheet.name, its just basically giving me the same tab value (the one that gets cut off) in the first row.

so is there no way i can use the ctl.name?

 

[boblarson]

so is there no way i can use the ctl.name?

The only way, because the way you currently have your code, would be to do all of the processing inside of the For Each ctl loop code.

 

[Summer123]

when i do that it screws up and creates few spreadsheet by overlaying information and insterting each querys name in rows 1, 2, 3 etc... so basically i think its going through each qeury and if it has a value it writes it but then goes through and if the second query has a value then overlays the first information but leaves row 1 with query 1 name and inserts query2 name in row2 excet the data is now gone for query1 and is overlaid by query2 and so and so forth...and then it evetually hangs and gives me an error... ughhh....

i'll send you the code in private message if thats ok?

 

[boblarson]

So, lay this out a bit more for me. What are you trying to do with the export? How does the ctl work in all of this? Are you trying to export a query that is named the same as the control (checkbox) and then the code normally puts it into a separate worksheet? If so, I have some code which might benefit you instead of trying to use the DoCmd stuff.

 

[Summer123]

ok so to explain, i have about 50+ queries which i have checkboxes for, the user can do one of two things

1.) User can select all which will select all 55 checkboxes and run through and only export the queries that have true output (not the blank queries with no results) to excel and format it using excel automation so it looks user friendly
2.) User can also pick and choose the queries they want and again only export the queries that have true output to excel

It was working fine however when it exported to excel some of the query names were long and it did output (and the query name was written in the tab name but got cut off) but the user didn’t know what that query was for since it would get cut off…so I wanted to see if I could print the query name or even the checkbox name out on the spreadsheet in the first row for each tab.

Thank you very much for your help on this Bob!

 

[boblarson]

Sorry, but am going to be squished for time the rest of the day. So, I will have to do it tomorrow. Hope that will be fine.