1

With Excel 2013 VBA, I am familiar with setting the value of a Workbook variable with:

    Set oWBSource = Workbooks.Open(strFileToOpen) 'Works well.

In my situation though, sometimes the Workbook is already open on the desktop. I am trying to find a more elegant way to "select" that spreadsheet and then attach my variable to that workbook to operate on. The code is running on a separate "master" spreadsheet.

Currently, I am asking the user if the file is already open. If so, then: 

    Dim rng As Range
    Set rng = Application.InputBox("Select any cell on workbook to operate on.", "Click Workbook Cell", Type:=8) 'Stops code and allows user to select a cell on an open spreadsheet
    Dim sTest As String 'variable used to test. 
    sTest = ActiveCell.Value 'Testing to see what value code is seeing. 
    sTest = ActiveWorkbook.Name 'Testing to see what value code is seeing
    Set oWBSource = Workbooks(ActiveWorkbook.Name) 'Works if selected Workbook is maximized and minimized.

If I run the above code and when it runs the inputbox, if I click one cell on the spreadsheet I want the code to operate on and allow the code to continue, the variables still reference the Workbook where the code resides rather than the "selected" workbook. If, while selecting one cell, I also maximize and minimize the spreadsheet (basically Activate it) and then click a cell and allow the code to continue, the active spreadsheet is set and it seems to work. I realize the cell reference is doing nothing. But the pause allows me to "Activate" the desired spreadsheet.

I am looking for a more elegant way to handle attaching my code to an already open spreadsheet. My current solution is clunky. Isn't there a way to have the user select the open spreadsheet and then have my code "act" on that spreadsheet?

I know this is a weird question. Be kind rating it please??!!

Comintern
  • 21,855
  • 5
  • 33
  • 80
cboshdave
  • 75
  • 1
  • 9
  • 1
    Why use `sTest = ActiveCell.Value`, for it just to be overwritten on the next line? Also, I think all you need to do is make this the last line: `Set oWBSource = Workbooks(sTest)`, unless I'm missing something. I ***highly*** suggest reading/studying [How to avoid `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – BruceWayne Sep 14 '16 at 16:21
  • Agreed... as I commented, that was simply for testing purposes. I was trying to make it easy for someone to "test" the code. – cboshdave Sep 14 '16 at 16:25
  • I don't quite understand - with `oWBSource` being set at the end, can't you just run your code inside a `With oWBSource` block? – BruceWayne Sep 14 '16 at 16:31
  • No. The `With oWBSource` block references the spreadsheet where the code lives rather than the clicked on spreadsheet. UNLESS I maximize and minimize the selected sheet first. Then it works correctly. I am trying to find a way to make the selection more elegant. – cboshdave Sep 14 '16 at 16:34
  • So the code you posted isn't the code that you're running? It's code for the user to select the worksheet, and then run some other code on that worksheet? After the user selects a range, `oWBSource` changes to reference that sheet - this sheet may or may not have code. If you create a new workbook, and then select cell `A1` on that new workbook, there's *no code* there, which is why I'm confused when you say the block references the sheet where the code lives. – BruceWayne Sep 14 '16 at 16:47
  • Yes. It's the code I am running. The code lives on sheet1.xlsm. When executed, I want the user to select sheet2.xlsx (already open). Then `oWBSource` "attaches" to sheet2.xlsx and operates on that sheet. The selected range is irrelevant. I was simply trying to use that to find what worksheet to operate on. The code will do the rest. – cboshdave Sep 14 '16 at 17:05
  • Clearly, the easy answer is to have the user close/save the worksheet to be acted upon and then simply execute the `Set oWBSource = Workbooks.Open(strFileToOpen)`. I was just hoping there was a more elegant solution that i was missing. Possibly no straightforward solution? – cboshdave Sep 14 '16 at 17:10

1 Answers1

1

If I understand correctly what you're trying to do, just use the rng to resolve the selected Workbook:

Set oWBSource = rng.Parent.Parent

rng.Parent is the Worksheet a Range is a part of, and the .Parent of a Worksheet is its Workbook.

Comintern
  • 21,855
  • 5
  • 33
  • 80