Re: Format Excel Report Output of SQL Queries (736 Views)
Reply
Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 1 of 15 (819 Views)

Format Excel Report Output of SQL Queries

Hi,

 

In Analysis view - Execl Reprot; i want to format the Excel report generate from SQL queries like below:

 

 

First Row - BOLD

First Row - Word Wrap Applied

Auto-Expand To Width of Data in Column

 

Orientation - Landscape

Paper Size - Legal 

Esteemed Contributor
William Schmitt
Posts: 342
Registered: ‎04-02-2008
Message 2 of 15 (807 Views)

Re: Format Excel Report Output of SQL Queries

That is what post-processing is for (which you asked about in another thread).  It is basically Excel macro code that will act on the results returned from the SQL.  I would recommend that you open the Excel created from your SQL and record a macro that will do what you want.  Then take this code and use it to build your post-processing.

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 3 of 15 (798 Views)

Re: Format Excel Report Output of SQL Queries

Hi William,

 

Thanks for your advice.

 

I am very new in VB; i can record the macro in Excel; but i have no experiance to develop the VB in Post_processing.

 

It will be so kind for me if you can share a sample code of how to implement this on Post_processing; i will be very thankfull to you....

 

 

Thanks 

Esteemed Contributor
William Schmitt
Posts: 342
Registered: ‎04-02-2008
Message 4 of 15 (794 Views)

Re: Format Excel Report Output of SQL Queries

But that is how you learn.  By recording a macro to do something in Excel, you can view the recorded code to see how you would code it yourself.  Then you can use this code to place in the post-processing section in QC.

 

You will never learn if you don't try it yourself.

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 5 of 15 (785 Views)

Re: Format Excel Report Output of SQL Queries

Hi William,

 

Ok, i am doing this.. try to implement the code which i got from recorded macro...

 

Please if i am stuck in somewhere in code - please advise. me....

 

Thank you...

Esteemed Contributor
William Schmitt
Posts: 342
Registered: ‎04-02-2008
Message 6 of 15 (776 Views)

Re: Format Excel Report Output of SQL Queries

It is difficult to do this for you, but I will tell you what I did, so you can duplicate.

  1.  In QC, create a new Excel report.
  2.  Add a SQL to return some data and test it to make sure it works.
  3.  Generate the report and select to open in Excel afterwards.
  4.  In Excel, turn on macro recording and do what you want to do (ie make the first row bold, warp text, autofit, etc. 
  5.  Look at the macro you just recorded and copy it.  The code will look something like below.
  6.  Go back to QC and paste that code into the Post-Processing section.

 

Sub Macro1()
'
' Macro1 Macro
'
    Range("A1:B1").Select
    Selection.Font.Bold = True
    Selection.WrapText = True
        
    Columns("A:B").EntireColumn.AutoFit
    
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .PaperSize = xlPaperLegal
    End With
    Application.PrintCommunication = True

End Sub

  

The next time you generate the report, the SQL will return your data, Excel will open and the post-processing code will run.

 

You really need to know how to do basic VBA coding and be familiar with the Excel object model.

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 7 of 15 (771 Views)

Re: Format Excel Report Output of SQL Queries

Hi William,

 

I did exactly what you tell,

 

Now for the time bing i just do one thing to be familier with:

 

I did only Bold of First Row of Excel:

 

and i get this code:

 

************************

 

Sub BOLD()
'
' BOLD Macro
'

'
Range("A1:L1").Select
Selection.Font.BOLD = True
End Sub

 

***************************

 

Now in Post_processing I have pre_written code: Like below so how can i impliment the above Macro code here..

 

*********************************

 

Sub QC_PostProcessing()

Dim MainWorksheet As Worksheet
' Make sure your worksheet name matches!
Set MainWorksheet = ActiveWorkbook.Worksheets("Query1")
Dim DataRange As Range
Set DataRange = MainWorksheet.UsedRange
' Now that you have the data in DataRange you can process it.
End Sub

 

***********************************

 

Please show me once.... i can do rest of things.... i am confident...

 

Esteemed Contributor
William Schmitt
Posts: 342
Registered: ‎04-02-2008
Message 8 of 15 (768 Views)

Re: Format Excel Report Output of SQL Queries

Copy that macro code and paste it into post-processing code right after where it says "...you can process it." and before the End Sub.

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 9 of 15 (766 Views)

Re: Format Excel Report Output of SQL Queries

Hi William,

 

I got an Error... (See attached)...

 

advise please...

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 10 of 15 (763 Views)

Re: Format Excel Report Output of SQL Queries

Hi William,

 

I did it!!!!

 

 

Sorry I am little bit nud.... i did it thank you...

 

I am going to implement one by one... and let you know....

 

Thank you so much....

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 11 of 15 (755 Views)

Re: Format Excel Report Output of SQL Queries

Hi William,

 

I have problem with Header : it can not retrive the "Current Date" in Post_processing... Even thoug it is not workin in Macro also...

 

I use Excel build in "Current Date" button  which  like below:

 

 

CenterHeader = "&""-,Bold""[2014davtest] &F" & Chr(10) & "Date: &D"

 

 

Please advise...

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 12 of 15 (746 Views)

Re: Format Excel Report Output of SQL Queries

Hi William,

 

I have tried with several ways but the "Current Date" or "Current Time" is not working  in Post_processign or even Run The Macro....

 

 

below is the code: 

 

.CenterHeader = "[2014davtest] &D&T"   

 

 

Please advise.

Frequent Advisor
Enk-A-Mania
Posts: 37
Registered: ‎04-16-2013
Message 13 of 15 (736 Views)

Re: Format Excel Report Output of SQL Queries

[ Edited ]

Try this
With ActiveSheet.PageSetup
   .CenterHeader = "[2014davtest] &D &T" 
End With

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 14 of 15 (723 Views)

Re: Format Excel Report Output of SQL Queries

Hi,

 

Date issue i solved by my self... by declare a variable....

 

 

 

Frequent Advisor
Anthony1bd
Posts: 49
Registered: ‎05-20-2014
Message 15 of 15 (721 Views)

Re: Format Excel Report Output of SQL Queries

Hi, 

 

I have another problem:

 

"&F" will return the file name with the "File Extension" but i need the only file name not the "File Extension".

 

Please advise....

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.