Essbase Retrieve

[mcyang]

How would I modify the following VBA code to retrieve on the active
workbook and active sheet? I'd like to send this template out and give
the users the option to modify the file name; however, the way it is
currently written, they would also need to modify the VBA; which is
burdensome. I'm a VBA novice, so please just directly modify my code.
THANKS!!!!!

Sub CopyRange()
' Retrieve Data
x = EssVRetrieve("[A - Essbase Template_SGA_Beta_07-08.xls]4-Report",
Range("B1:Q153"), 1)
' Replace missing values with zeros
Cells.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("E12").Select
End Sub

 

[Per Jessen]

Hi

Try this:

Dim wbName As String
wbName = ThisworkbookWorkbook.Name
x = EssVRetrieve("[" & wbName & "]4-Report", Range("B1:Q153"), 1)

Regards,
Per