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

Discussion in 'Microsoft Excel Programming' started by safe, Aug 19, 2004.

  1. safe

    safe Guest

    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 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
     
    safe, Aug 19, 2004
    #1
    1. Advertisements

  2. Re: 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 _
    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 :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 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
     
    Dave Peterson, Aug 20, 2004
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. turk5555
    Replies:
    2
    Views:
    300
    turk5555
    Dec 15, 2003
  2. Marcello do Guzman
    Replies:
    1
    Views:
    313
    Rocky McKinley
    Dec 16, 2003
  3. Pilgrim

    setting a range variable equal to the value of a string variable

    Pilgrim, Jul 1, 2004, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    1,237
    Pilgrim
    Jul 1, 2004
  4. Kobayashi

    Can a workbook variable be set by clicking the workbook?

    Kobayashi, Oct 28, 2004, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    201
    Bob Phillips
    Oct 28, 2004
  5. Buffyslay

    select offset (variable ,1) to offset(variable ,variable)

    Buffyslay, Nov 15, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    388
    Bob Phillips
    Nov 15, 2006
Loading...

Share This Page