PC Review


Reply
Thread Tools Rate Thread

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

 
 
safe
Guest
Posts: n/a
 
      19th Aug 2004
hello all (my first post )

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 Before:=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 Before:=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

 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      20th Aug 2004
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 _
before:=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 )
>
> 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 Before:=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 Before:=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
http://www.pcreview.co.uk/forums/(E-Mail Removed)
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
select offset (variable ,1) to offset(variable ,variable) Buffyslay Microsoft Excel Programming 1 15th Nov 2006 12:45 PM
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Daniel Microsoft Excel Worksheet Functions 1 9th Jul 2005 03:05 AM
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Daniel Microsoft Excel Discussion 1 9th Jul 2005 01:04 AM
Can a workbook variable be set by clicking the workbook? Kobayashi Microsoft Excel Programming 2 28th Oct 2004 06:20 PM
setting a range variable equal to the value of a string variable Pilgrim Microsoft Excel Programming 2 1st Jul 2004 11:32 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:13 AM.