Building excel report with ssrs link - how to pass dynamic date parameters entered in excel sheet

Question

I have excel report using ssrs report link -

http://Serverlink/ReportServer?%2fFinance%2fReportname&rs:Command=Render&FromDate=01/31/2016&ToDate=03/13/2016&rs:Format=Excel . This works fine when I use it in Macro to run report on button click command

Private Sub ViewReport_Click()
Workbooks.Open Filename:= _
"http://Serverlink/ReportServer?%2fFinance%2fReportname&rs:Command=Render&FromDate=01/31/2016&ToDate=03/13/2016&rs:Format=Excel"

ActiveSheet.Range("A8:I2000").Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Windows(ThisWorkbook.Name).Activate
Range("A8").Select
ActiveSheet.Paste
End Sub

But I need to pass dynamic dates entered in excel sheet to url link- in DTPicker. How can I acheive it?

Ecxel macro capture

Answer 1

You can simply read the dates from the spreadsheet into variables and format them. Then concatenate those variables with the URL.

Assuming your beginning date is in cell "A1" and your end date is in cell "A2".

Dim fromDate As String
Dim toDate As String

fromDate = Format(Range("a1").Value, "dd/mm/yyyy")
toDate = Format(Range("a2").Value, "dd/mm/yyyy")


Workbooks.Open Filename:= _
"http://Serverlink/ReportServer?%2fFinance%2fReportname&rs:Command=Render&FromDate=" & fromDate & "&ToDate=" & toDate & "&rs:Format=Excel"