Usin OpenDiagBox to set ActiveWorkbook Name

[Vacuum Sealed]

Hi Everyone

Is there a way that I can utilise the "FileToOpen" as a pointer for
activating a Workbook.

eg.

Sub ImportData()


ChDrive "T:\"
ChDir "T:\MyFolder"
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")

If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Doh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If


Range("A5:AD201").Select
Selection.Copy

Windows(MyOtherWorkbook).Activate
Sheets("MySheet").Select
ActiveSheet.Paste

' This is the tricky bit, Once I have opened the "FileToOpen", and moved the
focus away from it to "MyOtherWorkbook, can I then use the "FileToOpen"
' statement inside the brackets to then re-establish the focus on the
Workbook that was opened originally.

Windows(FileToOpen).Activate

Either that, or have the file name Stamped/Inserted into the bracket.

eg.

If for instance, using the OpenDiagBox to get my file, I selected
"Duff_Beer", is there a handy line of code that can Imprint/Insert/Stamp
that between the brackets so the next instance of Windows().Activate could
be populated with Windows("Duff_Beer").Activate.

I only need to have this happen once, Although I will be copying data from
two locations and pasting to two locations in the target Workbook, I will be
copying the first range when I Open/Activate the Source, its the second
instance that is of concern, reason being is that I need to copy data from a
second sheet and paste to seperate locations in my target Workbook, so I
would need to activate the Source twice.

TIA
Mick

 

[GS]

Vacuum Sealed submitted this idea :

Hi Everyone

Is there a way that I can utilise the "FileToOpen" as a pointer for
activating a Workbook.

eg.

Sub ImportData()


ChDrive "T:\"
ChDir "T:\MyFolder"
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xls (*.xls),")

If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Doh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If


Range("A5:AD201").Select
Selection.Copy

Windows(MyOtherWorkbook).Activate
Sheets("MySheet").Select
ActiveSheet.Paste

' This is the tricky bit, Once I have opened the "FileToOpen", and moved the
focus away from it to "MyOtherWorkbook, can I then use the "FileToOpen"
' statement inside the brackets to then re-establish the focus on the
Workbook that was opened originally.

Windows(FileToOpen).Activate

Either that, or have the file name Stamped/Inserted into the bracket.

eg.

If for instance, using the OpenDiagBox to get my file, I selected
"Duff_Beer", is there a handy line of code that can Imprint/Insert/Stamp that
between the brackets so the next instance of Windows().Activate could be
populated with Windows("Duff_Beer").Activate.

I only need to have this happen once, Although I will be copying data from
two locations and pasting to two locations in the target Workbook, I will be
copying the first range when I Open/Activate the Source, its the second
instance that is of concern, reason being is that I need to copy data from a
second sheet and paste to seperate locations in my target Workbook, so I
would need to activate the Source twice.

TIA
Mick

Create a variable to hold a ref to the workbook...
dim wkbFileToOpen As Workbook

...then in your Else clause of the If...Then construct:

change...
Workbooks.Open Filename:=FileToOpen

to...
Set wkbFileToOpen = Workbooks.Open(Filename:=FileToOpen)

then use it like this...
wkbFileToOpen.Activate

---BUT---

You may want to consider revising your code because you don't need to
activate or reactivate anything to copy/paste from one to the other.
For example...

Use vars to hold refs to each wkb:
Dim wkbSource As Workbook, wkbTarget As Workbook

Then load your wkbs into each var...
Set wkbSource = Workbooks.Open(Filename:=FileToOpen)
Set wkbTarget = workbooks("MyOtherWorkbook")

Now copy/paste in one op:
wkbSource.Range("A5:AD201").Copy
Destination:=wkbTarget.Sheets("MySheet").Cells(1)

 

[Vacuum Sealed]

Again

Thx heaps Garry

I will also post code as this is working like a charm.

Cheers
Mick.

 

[GS]

Vacuum Sealed submitted this idea :

Again

Thx heaps Garry

I will also post code as this is working like a charm.

Cheers
Mick.

You're welcome! Glad to be of help...