0

Ok I realize this looks like a duplicate, but I assure you it is not, at least not after scouring the internet for 2 hours!

Here are all the other related links that I read carefully before resulting to writing up a question: https://www.cfo.com/spreadsheets/2011/08/referring-to-other-worksheets-or-workbooks-in-an-excel-macro/

https://www.excelcampus.com/vba/copy-paste-another-workbook/

"Run-Time error '438': Object doesn't support this property or method." Range.values = Range.values

https://www.reddit.com/r/excel/comments/akrxxu/vba_referencing_worksheets_by_codename_in/

https://www.thespreadsheetguru.com/blog/vba-guide-to-referencing-excel-worksheet-tabs

https://learn.microsoft.com/en-us/office/vba/excel/concepts/workbooks-and-worksheets/refer-to-sheets-by-name

https://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

https://www.techrepublic.com/blog/10-things/10-ways-to-reference-excel-workbooks-and-sheets-using-vba/

How to refer to a Excel Worksheet by its VBA Object Name in another Workbook?

Excel tab sheet names vs. Visual Basic sheet names

VBA Getting data from another Sheet

How to copy sheets from a workbook to another workbook

EXCEL VBA: Copy Sheet from a workbook to another workbook in different location

Problems pasting values on another workbook sheet on vba

Reference an excel sheet from another workbook without copying the sheet

So I read on a number of the above links that it is not possible to directly reference a sheet object from another workbook: Specifically these links: https://www.reddit.com/r/excel/comments/akrxxu/vba_referencing_worksheets_by_codename_in/

This link says you cannot do this but only when referencing the CodeName property of the Sheet object: https://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

However this link from Microsoft shows you exactly how to do it: https://learn.microsoft.com/en-us/office/vba/excel/concepts/workbooks-and-worksheets/refer-to-sheets-by-name

So enough about my research on this problem.

Let me state my problem clearly.

I've got a workbook that is open and running a macro. I've got many other workbooks open, specifically many source workbooks. 1 single destination workbook. So looking at the source workbook I've got a lot of code that is getting that file and opening it. All of that is working perfectly up to this point.

So it all breaks down to just a few lines of code where the problem is happening.

Dim objSourceWorkbook
Dim objSourceSheet
Dim strSourceSheetName As String
strSourceSheetName = "Some complicated sheetname" 'I've confirmed the correct file is being open and the sheet name is EXACTLY as assigned to the sheet name.
Dim strSourceWorkbookFilename As String
strSourceWorkbookFilename = getFilenameFromPath(objFile) 'I've also confirmed the filename returned by this function is EXACTLY the same as the objSourceWorkbook.name. So that is working.
Set objSourceWorkbook = Workbooks.Open(Filename:=objFile)
objSourceSheet = objSourceWorkbook.Sheets(strSourceSheetName) 'This didn't work.

So let me now show you all the other various ways I've attempted to get the sheet object:

objSourceSheet = Sheets(strSourceSheetName)
objSourceSheet = objSourceWorkbook.Worksheets(strSourceSheetName)
objSourceSheet = objSourceWorkbook.Worksheet(strSourceSheetName)
objSourceSheet = Workbooks(strSourceWorkbookFilename).Worksheets(strSourceSheetName)
objSourceSheet = Workbooks(strSourceWorkbookFilename).Sheets(strSourceSheetName)

So I thought maybe I need to activate the workbook that I'm trying to get the sheet for. So I further tried every single combination above with the following combinations of the Activate call.

objSourceWorkbook.Activate
Worksheets(strSourceSheetName).Activate

So that's a total of 18 different attempted combinations.

In every attempt I get the same typical Microsoft meaningless error:

Run-time error '438': Object doesn't support this property or method

At this point I'm at a loss for what would make this work. I know I've hit this problem before and I remember it was really difficult to solve, but I was able to solve it. However, that code has long since been lost since it was for a government contract more than a decade ago.

Maybe I'll try and simplify this problem to just 3 nearly empty workbooks and see if I can still reproduce this same issue.

Any ideas? I'd much appreciate it! Thank you in advance!! Cheers and stay safe out there people!

0m3r
  • 12,286
  • 15
  • 35
  • 71
Seth Eden
  • 1,142
  • 2
  • 20
  • 42
  • 2
    `Dim objSourceShet as Sheet`? I would recomend creating shorter variable names for readability. Something like `wb` for workbook and `ws` for worksheet. Makes things much easier to read... Also, add `Option Explicit` to your code. Given the long variable names, you may have a typo – urdearboy Mar 20 '20 at 19:17
  • 1
    Don't `Activate`... no need to normally. See [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Mar 20 '20 at 19:17
  • 2
    You're missing the necessary `Set` too... – BigBen Mar 20 '20 at 19:19
  • LOL Thanks guys....figured it out even before I looked at these comments...but you guys were spot on! :-D – Seth Eden Mar 20 '20 at 19:25
  • 2
    Nice catch @BigBen - per my first comment, this error would have likely stood out and been much more obvious if you group your code (Dim statements and Set statements together) and use more ideal variables. This question is a pretty good example of how 'best practices' likely evolved lol – urdearboy Mar 20 '20 at 19:33

1 Answers1

2

Ok I think I found the source of my problem.

Dim objSourceWorkbook As Workbook
Dim objSourceSheet As Worksheet

Set objSourceSheet = objSourceWorkbook.Worksheets(strSourceSheetName)

First needed to declare the As Workbook & As Worksheet Second use the Set keyword in front of the assignment...DUH!!! Smacks hand on forehead

Thanks for helping me at least state and think through my problem clearly. :-D

urdearboy
  • 14,439
  • 5
  • 28
  • 58
Seth Eden
  • 1,142
  • 2
  • 20
  • 42