0

Here's my problem :

I have a sub in my main file (let's call it mainFile)doing this :

-Opening a file (the user chooses which file to open)

-Retrieve the name of this file in a variable

-Copy value of a few cells in file1, paste them in mainFile

in order to do that, my code looks something like this :

Workbooks.Open file1
fileName = ThisWorkbook.Name
Set source = Workbooks(fileName).Sheets("Feuil1")
Set target = Workbooks(mainFile).Sheets("Feuil1")
source.Range("A5:A66").Copy target.Range("T5")

but the variable fileName, instead of returning, for instance, "file1.xls", returns "mainFile.xls".

I guess it's because the sub is located in mainFile. But what can I use instead of ThisWorkbook.name ?

Thanks

Community
  • 1
  • 1
l3x0rcist3
  • 1
  • 3

2 Answers2

2

You seem to be doing extra work here that you don't need. We can set a Workbook variable, and use it directly, instead of accessing it by name.

Dim wkbk as Workbook

Set wkbk = Workbooks.Open(file1)
Set source = wkbk.Sheets("Feuil1")
Set target = ThisWorkbook.Sheets("Feuil1")
source.Range("A5:A66").Copy target.Range("T5")

The other option that you have (which is not a good choice, because it can lead to strange and hard to debug errors) is to use ActiveWorkbook, which references the displayed workbbok. In this case, that would be the workbbok you just opened.

Degustaf
  • 2,655
  • 2
  • 16
  • 27
1

That's one of the issues when using This...

Try this code, instead:

Dim NewWB as workbook

Set NewWB = Workbooks.Open file1
fileName = NewWB.Name
Set source = Workbooks(fileName).Sheets("Feuil1")
Set target = Workbooks(mainFile).Sheets("Feuil1")
source.Range("A5:A66").Copy target.Range("T5")

Read How to avoid using Select in Excel VBA macros for tips on avoiding the This..., Active... and Select... statements to eliminate these (and other) types of issues.

Community
  • 1
  • 1
FreeMan
  • 5,660
  • 1
  • 27
  • 53
  • I use ThisWorkBook very often - it just refers to the workbook containing the code that's running, which is often the workbook I'm after. – Darren Bartrup-Cook Jun 05 '15 at 14:27
  • I'm sure you do, and you've gotten to be reasonably good at it. Both my solution and Degustaf's suggest eliminating the `ThisWorkbook` reference and you seem quite thrilled with his(?) response. I simply went a bit further to offer some reading material on how to avoid this type of issue in the future. It's entirely up to you whether you choose to read it & take action on it. – FreeMan Jun 05 '15 at 14:35
  • Not quite sure who's response I'm meant to be thrilled with. Yes as you've both offered - there's alternatives to using ThisWorkbook. I was just trying to clarify what ThisWorkbook actually referred to. I don't believe anything should _always_ be avoided. It depends on the situation. – Darren Bartrup-Cook Jun 05 '15 at 14:59