Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Reporting in Excel by data source (SQL Server)

    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

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 Posts

  4. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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
    Last edited by farrukh; 2013-08-11 at 02:11. Reason: data in excel and getting from

  5. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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

  6. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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

  7. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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

  8. #7
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 Posts
    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  'CHANGE QUERY1 TO NAME OF QUERY
    Worksheets("ReportTemplate").Range("A1").CopyFromRecordset rcrdset
    
    '-------OR----------
    
    Const Querystr As String = "SELECT * FROM table1 WHERE [datefield] Between #8/1/2013# And #8/8/2013#;"
    rcrdset.Open Querystr, connection1
    Last edited by Maudibe; 2013-08-11 at 15:05.

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    farrukh (2013-08-12)

  10. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  11. #9
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2013-08-13)

  13. #10
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Dear Zeddy
    Many thanks for your solution version. One thing to ask is this calender selection dates embedded to the code Tester?

    Regards
    farrukh
    Last edited by farrukh; 2013-08-14 at 05:35. Reason: embedded

  14. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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
    Attached Files Attached Files

  15. #12
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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?


    Capture.JPGCapture.JPG

  16. #13
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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

  17. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2013-08-14)

  18. #14
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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

  19. #15
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •