Trouble Sending a Variable to Excel...

[rjw524]

Hi all,

Can anyone tell me why this use of a variable found using the "Let" command is passing the variable to Excel? Instead it's passing along either "%WhereYaAt%" or "WhereYaAt" depending on whether or not I enclose the variable with %.

Code: Select all

VBSTART
Dim objExcel
Sub GetExcel
    Set objExcel = GetObject( , "Excel.Application")
End Sub
VBEND
  
VBRun>GetExcel
VBEval>objExcel.ActiveSheet.Name,Sheet
VBEval>objExcel.ActiveCell.Address,Cell
Let>WhereYaAt='%Sheet%'!%Cell%

SetFocus>Microsoft Excel - Master.xlsm
wait 1.0

Press F5
SK_DELAY=10
Send 'Access DBN'!A5
SK_DELAY=10
Press Enter
wait 0.2

Press CTRL
Press END
Release CTRL

wait 0.1

Press Left * 2

Press CTRL
Send c
Release CTRL

Press>F5
Press>Backspace
Send>%WhereYaAt%

wait>0.5

Press>Enter

Been wrestling with this for a couple days now.

P.S. I'd rather not use the "Let>VAREXPLICIT=1" code as that sends a lot of the code I already have written haywire...

Thanks!

[Marcus Tettmar]

I can't see anything untoward at all here. I did a little test using much the same code and it worked fine for me. Obviously I don't have the same workbook as you so I made a new blank one, saved it as Master.xlsx and just ran this code:

Code: Select all

VBSTART
Dim objExcel
Sub GetExcel
    Set objExcel = GetObject( , "Excel.Application")
End Sub
VBEND
  
VBRun>GetExcel
VBEval>objExcel.ActiveSheet.Name,Sheet
VBEval>objExcel.ActiveCell.Address,Cell
Let>WhereYaAt='%Sheet%'!%Cell%


SetFocus>Master.xlsx*
wait 1.0
Send>%WhereYaAt%

Works fine - I ended up with "'Sheet1'!$B$2" in the active cell. (which happened to be B2 at the time of course).

[JRL]

P.S. I'd rather not use the "Let>VAREXPLICIT=1" code as that sends a lot of the code I already have written haywire...

To prevent affecting all of your code just use "VAREXPLICIT=1" in the precise location you need it. The moment you don't need it set VAREXPLICIT=0 and the rest of your variable resolving will return to default behavior.

I do this with most system variables in code I write today. Saves a lot of headaches down the road when you alter a script long after you've forgotten all the details of the script.

[rjw524]

Hi Marcus and JRL,

Thanks for taking a look and testing it for me!

After getting confirmation that the code was written correctly, I focused on the steps higher up in the macro and found the problem. (it's a long macro)

FAR up in the macro I made a mistake that was unintentionally not allowing the variable to populate correctly.

So, thanks again!

Consider this solved.

rjw