Referencing workbooks opened with GetOpenFilename

[AlexCS]

Hi everyone,

I cannot seem to be able to work with the GetOpenFilename on the following piece of code (PLbook is the workbook containing the macro)

Dim CApath As Variant
Dim PLbook As Variant

ThisWorkbook.Save
CApath = Application.GetOpenFilename(Title:="Close Allocation Model")
Set wbOpen = Workbooks.Open(CApath)
PLbook = Application.GetOpenFilename(Title:="Commercial Packed P&L")
ThisWorkbook.Save
Set wb2Open = Workbooks.Open(PLbook)

Workbooks(wb2Open).Sheets("Act Plant Exp").Cells(6, 2).Value = Workbooks(wbOpen).Sheets("Bag in Box").Cells(52, 14).Value

If I use wb2Open and wbOpen the code does not crash but it does not do what it is meant to do either, namely attribute the value of a cell in the CApath workbook to another cell in the PLbook.

If I try to use CApath and PLbook instead of wbOpen I get a Subscript out of range error.

If I open the files prior to running the macro and mention their name via Input box the code works but unfortunately my users find it too difficult.

Please lend a hand, I have seen my code crash in every possible way since working on this!

Many thanks,

Alex

 

[Trevor G]

Alex within you code are you refering to the file extension as I cannot see them?

Perhaps this will help.

Dim CApath As Variant
Dim PLbook As Variant

ThisWorkbook.Save
CApath = Application.GetOpenFilename(Title:="Close Allocation Model")
Set wbOpen = Workbooks.Open(CApath) & ".xls"
PLbook = Application.GetOpenFilename(Title:="Commercial Packed P&L")
ThisWorkbook.Save
Set wb2Open = Workbooks.Open(PLbook) & ".xls"

 

[AlexCS]

Hi Trevor,
Thank you for your suggestion. The file extensions are included in the variables CApath and PLbook as part of GetOpenFilename and if I try to add them to wbOpen and wb2Open I get Run-time error 436 Object does not support property or method (I cannot even Msgbox wbOpen and wb2Open as they have not been declared and will prompt an error if declared as strings)
In the end, my problem is reduced to this:
If I use wbOpen and wb2Open as my document paths nothing will happen whatsoever afer running the below formula
Workbooks(wb2Open).Sheets("Act Plant Exp").Cells(6, 3).Value = Workbooks(wbOpen).Sheets("Bag in Box").Cells(53, 14).Value<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If I use PLbook and CApath as my document paths I get Run-time error – Incorrect function when running the below piece of code
Workbooks(PLbook).Sheets("Act Plant Exp").Cells(6, 3).Value = Workbooks(CApath).Sheets("Bag in Box").Cells(53, 14).Value<o></o>
Is there any other way of referencing my two workbooks so that the formula actually works?
Kind regards,
Alex
<o> </o>

 

[Trevor G]

Alex try this out and see what the code states.

Record a macro to open the 2 books and create the formula then stop recording, that will give you the basic code and then it can be adjusted.

 

[Firefly2012]

Hi Alex

You have references to the workbooks so you can use these to refer to the sheets - you do not wrap with Workbooks() ie:

Replace

Workbooks(wb2Open).Sheets("Act Plant Exp").Cells(6, 2).Value = Workbooks(wbOpen).Sheets("Bag in Box").Cells(52, 14).Value

With:

wb2Open.Sheets("Act Plant Exp").Cells(6, 2).Value = wbOpen.Sheets("Bag in Box").Cells(52, 14).Value