excel VBA problem - setting workbook as variable & opening/re-opening
 

hello all (my first post :eek: )

first of all, i'm delighted to have found this forum, having taugh
myself as much as i can of Excel VBA (& enjoyed doing so) its good t
find others with a similar interest

anyway, the reason i got here is, i'm having a problem & woul
appreciate some assistance

i'm trying to use vba to open a workbook, (which i don't know the nam
of so the workbook name is a variable) once open i'm copying a shee
from this workbook into my workbook (so my master workbook is active
the variable workbook isn't) I then wish to close the "variable
workbook so i need to re-activate this workbook to close

the problem i'm having is that i've tried this 2 ways & neither wil
work. i either can't set the variable as a workbook, or i can't cal
the workbook i've set as a variable

the code is as below, this may explain what i'm doing better than i ca


*version 1* - declaring variable as workbook, won't allow me to set th
variable (i get an "object required" error at line 2)

Sub import1()

Dim myfile1 As Workbook

Set myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1

Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Befo=Workbooks("master").Sheets(1)

myfile1.Activate
ActiveWorkbook.Close False

End Sub

*version 2* not delaring variable allows the file to be opened & th
sheet to be imported into the master sheet, but fails at line 7 when
try to re-activate my variable workbook (subscript out of range error
I've tried workbooks(-name-).activate also


Sub import1()

myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1

Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Befo=Workbooks("master").Sheets(1)

Windows(myfile1).Activate
ActiveWorkbook.Close False

i suspect i've made a basic mistake, but as i've said, i'm self taugh
so there are a lot of gaps in my knowledge!

any & all suggestions gratefully received - thank

--
Message posted from http://www.ExcelForum.com

excel VBA problem - setting workbook as variable &opening/re-opening
 

dim mstrwkbk as workbook
dim wkbk as workbook
dim myFileName As Variant

myFileName = Application.GetOpenFilename
If myFileName = False Then
Exit Sub 'user hit cancel
End If

set mstrwkbk = activeworkbook 'workbooks("master.xls") '???

set wkbk = workbooks.open(filename:=myfilename)

wkbk.worksheets("data").copy _
befo=mstrwkbk.worksheets(1)

wkbk.close savechanges:=false


=====
You can do lots of things without selecting. Just refer to those objects
directly.


"safe <" wrote:

hello all (my first post :eek: )

first of all, i'm delighted to have found this forum, having taught
myself as much as i can of Excel VBA (& enjoyed doing so) its good to
find others with a similar interest

anyway, the reason i got here is, i'm having a problem & would
appreciate some assistance

i'm trying to use vba to open a workbook, (which i don't know the name
of so the workbook name is a variable) once open i'm copying a sheet
from this workbook into my workbook (so my master workbook is active &
the variable workbook isn't) I then wish to close the "variable"
workbook so i need to re-activate this workbook to close

the problem i'm having is that i've tried this 2 ways & neither will
work. i either can't set the variable as a workbook, or i can't call
the workbook i've set as a variable

the code is as below, this may explain what i'm doing better than i can

*version 1* - declaring variable as workbook, won't allow me to set the
variable (i get an "object required" error at line 2)

Sub import1()

Dim myfile1 As Workbook

Set myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1

Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Befo=Workbooks("master").Sheets(1)

myfile1.Activate
ActiveWorkbook.Close False

End Sub

*version 2* not delaring variable allows the file to be opened & the
sheet to be imported into the master sheet, but fails at line 7 when i
try to re-activate my variable workbook (subscript out of range error)
I've tried workbooks(-name-).activate also

Sub import1()

myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1

Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Befo=Workbooks("master").Sheets(1)

Windows(myfile1).Activate
ActiveWorkbook.Close False

i suspect i've made a basic mistake, but as i've said, i'm self taught
so there are a lot of gaps in my knowledge!

any & all suggestions gratefully received - thanks

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson