Reporting in Excel by data source (SQL Server)

Topic

New Reply

Hello Experts !
I need to know that is there good Add-in /tool that i can create report in excel user defined format by selecting the date from calender in excel and get the desired date data in excel from sqlserver Database?

many thanks

Reply | Quote

Replies

This might have something to offer you:

How to import data from Microsoft SQL Server into Microsoft Excel

Reply | Quote

Thanks Maudibe
But how to control dates in excel ( means retrieve only desired one date data or by selecting date range data in excel and getting from sql server database for reporting purpose

Reply | Quote

Hi farrukh

..are you asking us to show you how to use the Excel calendar control?
..or, if you know how to use it, are you asking us how to use the selected date in vba with a sql get data command???

zeddy

Reply | Quote

Respected Zeddy 🙂

Like i have a report template in excel and i want only retrieve the data for the date which i need e.g 1/1/2012. I have a idea to get data from database to excel sheet but i need that by selecting a example 1/1/2012 some thing like calender on excel sheet the data comes only for selecting date from excel.

Thanks

Reply | Quote

Hi farrukh

..if you posted a sample of what you are doing, it would be so much easier to give you an elegant solution.

zeddy

Reply | Quote

farrukh,

Will you be drawing recordsets from a query that has already been filtered by date or by filtering recordsets from a table? Below are 2 untested code segments that may point you in the right direction. They will need to be modified and expanded on.

HTH,
Maud

Code:

Dim connection1 As New ADODB.Connection
Dim rcrdset As New ADODB.Recordset
‘—————————–

rcrdset.Open “Select * From Query1″, connection1  [COLOR=”#008000”]’CHANGE QUERY1 TO NAME OF QUERY[/COLOR]
Worksheets(“ReportTemplate”).Range(“A1”).CopyFromRecordset rcrdset

‘——-OR———-

Const Querystr As String = “SELECT * FROM [I][B][COLOR=”#FF0000″]table1[/COLOR][/B][/I] WHERE [I][B][COLOR=”#FF0000″][datefield][/COLOR] [/B][/I]Between #8/1/2013# And #8/8/2013#;”
rcrdset.Open Querystr, connection1

Reply | Quote

Zeddy/Maudibe,

The sample sheet having a module name “CALL_DB_DATA” which contains on code name Tester. Is it possible that the below code area i mean the date selection area will call from excel button then the pop up menu comes for both ranges like calendar selection of different ranges availablity?

subArray(3) = CDate(“4/7/2012”)
subrAray(4) = “TO_DATETIME”
subArray(5) = CDate(“11/11/2012”)

Thanks

Reply | Quote

Hi Farruk

I have attached my version of your submitted file.

I have added some named cell ranges for the [startDate] and [endDate]
These cell values can be easily read with vba.

I have assigned macros to the blue [Start Date] and [End Date] buttons.
This will display a month calendar, to allow a date to be clicked.

I have assumed that start and end dates cannot be in the future, so this is automatically disallowed via the vba code.

If you select a start date that is later than the current end date, then the end date will be automatically set to the same start date.
If you select an end date that is earlier than the current start date, then the start date will be automatically set to the same end date.

You can use the clicker buttons to adjust the start and end dates as well.
NOTE: same rules apply: you can’t have a start date which is later than the end date, and you can’t have an end date which comes before the existing start date.

I’m sure you could make use of these.

zeddy

Reply | Quote

Dear Zeddy
Many thanks for your solution version. One thing to ask is this calender selection dates embedded to the code Tester?

Regards
farrukh

Reply | Quote

Hi Farrukh

The attached version has the vba Tester routine fetch the calendar dates, and assign them to the array required variables.

If you examine the code for the Tester routine, you will see this was easy to do.
Is this OK?

zeddy

Reply | Quote

Respected Zeddy,

Thanks i check that the dates which is provided from excel sheet shown in the zEndDate = [endDate] but i got the attached error?

34695-Capture34695-Capture

Reply | Quote

Hi Farrukh

..it was your code, I didn’t change that line!
..it gives an error because you typed it wrong.
It says
subrAray(4) = “TO_DATETIME”
you must fix the spelling mistake to..
subArray(4) = “TO_DATETIME”

zeddy

Reply | Quote

Respected Zeddy,

(: sorry i apologize … It works for me many thanks for your support and kindness God bless you always as you are helping people.

Thank you
farrukh

Reply | Quote

Hi Farrukh

Many thanks for letting me know it worked OK.

In my previous attached files, I updated your custom Function ExcelVersion(), in your module named Compatibility

This Function has now been updated to include Excel2010 and the new Excell2013.
If you were using either of these Excel versions, your Function would have said “Too Old!“, which, of course, they are not.
This has been fixed with my update.

zeddy

Reply | Quote

Respected Zeddy
you are highly appreciated you have done two different Threads in one 🙂

Many thanks
farrukh

Reply | Quote

Respected Zeddy,
I have upgraded MS Office 2007 to MS Office 2010. In Office 2007 the code work for me but in Office 2010 the Form does not load and it says attached error.

34976-Screenshot134977-Screenshot2

Your help always make people life easy…

Reply | Quote

Hi Farrukh

The Calendar Control (called MSCAL.OCX) was last shipped with Office 2007.
(It is usually included with Access, so would be part of Office Professional, which includes Access)
It is not included in later versions of Office.

In Office 2010 they have replaced this date picker with an updated version in the
Active-X library, called MSCOMCT2.OCX. This is for 32-bit Office (NOT Office-64-bit).
The MonthView calendar object is part of MSCOMCT2.OCX

On my laptop, this file MSCOMCT2.OCX is located in two folders:
C:WindowsSystem32mscomct2.ocx
C:WindowsSysWOW64mscomct2.ocx

(Both files mscomct2.ocx are the same file, size 659,264 bytes, file dated 24/03/2009)
Check whether you have this file on your computer.
If not, it is freely available for download here:
You can download Microsoft Windows Common Controls-2 6.0 from the following Microsoft Web site:
http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB

zeddy

Reply | Quote