Essbase Retrieve

M

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
 
P

Per Jessen

Hi

Try this:

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

Regards,
Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top