VB and an Excel Spreadsheet


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: VB and an Excel Spreadsheet

Hybrid View

  1. #1
    Dale Boyer Guest

    VB and an Excel Spreadsheet


    I'm writing a program in VB6 that accesses an Excel spreadsheet for storing,
    manipulating and printing out data. Here's what's happening:

    First, consider the following code, as copied from a recorded Excel macro:

    Range("A1:F56").Select
    Selection.Copy
    Sheets("LastDate").Select
    Range("A1").Select
    ActiveSheet.Paste

    I recorded that macro in an effort to troubleshoot the following from the
    program:

    TimeSheet.Range("A1:F56").Copy
    LastSheet.Select
    LastSheet.Range("A1").Select
    LastSheet.Range("A1").PasteSpecial

    This code WORKS. . .

    . . . IF I have the spreadsheet open at the same time, the code runs without
    error. HOWEVER, if the spreadsheet is closed (and mind you, everything else
    STILL works okay), I get the following:

    Runtime Error '1004':
    Select method of Range class failed.

    Selecting "Debug" highlights the third line (LastSheet.Range("A1").Select)
    as containing the offending code.

    I'd greatly appreciate ANY help.

    Dale Boyer
    boyers6@home.com
    dale.boyer@faa.gov


  2. #2
    mrfelis Guest

    Re: VB and an Excel Spreadsheet

    Dale Boyer <dale.boyer@faa.gov> wrote in message
    news:3a71ce82@news.devx.com...
    >
    > TimeSheet.Range("A1:F56").Copy
    > LastSheet.Select
    > LastSheet.Range("A1").Select
    > LastSheet.Range("A1").PasteSpecial


    The two selects's are only required to see what is going on.

    > This code WORKS. . .
    >
    > . . IF I have the spreadsheet open at the same time, the code runs

    without
    > error. HOWEVER, if the spreadsheet is closed (and mind you, everything

    else
    > STILL works okay), I get the following:


    What do you mean by closed?



  3. #3
    Dale Boyer Guest

    Re: VB and an Excel Spreadsheet


    I had accidently posted this to the wrong forum, but since you're attempted
    to help me (and I DO appreciate any help I can get), I'll try to make it
    clearer...

    What I'm trying to do is get away from having to have an Excel spreadsheet
    open, i.e., where the user can input data directly into the spreadsheet.
    By writing the interface in VB (and mind you, this IS the first time I've
    done this), I can store the data in the spreadsheet without ever having to
    even start Excel. But for troubleshooting purposes, I have opened the spreadsheet
    in a window on one side of my screen and watched the changes occur in it
    at the same time I'm watching the code as I step through it (F8). I hope
    all that makes sense.

    Anyway, as I step through it, one line at a time, I can watch (as this segment
    of code runs) the range A1:F56 copy from one sheet, and then paste to the
    other sheet, exactly as it is intended to do.

    But if I close out Excel, and THEN run the program, that's when I get the
    error. Everything else about the program works fine, except for that one
    line. The data is taken from the user input screen and placed in to the proper
    location in the spreadsheet (confirmed by opening it later and looking at
    the appropriate sheets), the calculations are performed on the data, etc.,
    etc. But when it gets to that line of code, THAT'S when I get the error.

    I'll try removing the 3rd line and see if that makes a difference. Although
    I might have already tried that... I've tried several different things, including
    ACTIVATE. In the meantime, I hope I've made a little more sense out of this.
    Let me know if you've got any ideas.

    Thanks in advance,

    Dale

    "mrfelis" <mrfelis@yahoo.NOSPAM.com> wrote:
    >Dale Boyer <dale.boyer@faa.gov> wrote in message
    >news:3a71ce82@news.devx.com...
    >>
    >> TimeSheet.Range("A1:F56").Copy
    >> LastSheet.Select
    >> LastSheet.Range("A1").Select
    >> LastSheet.Range("A1").PasteSpecial

    >
    >The two selects's are only required to see what is going on.
    >
    >> This code WORKS. . .
    >>
    >> . . IF I have the spreadsheet open at the same time, the code runs

    >without
    >> error. HOWEVER, if the spreadsheet is closed (and mind you, everything

    >else
    >> STILL works okay), I get the following:

    >
    >What do you mean by closed?
    >
    >



  4. #4
    mrfelis Guest

    Re: VB and an Excel Spreadsheet

    Dale Boyer <boyers6@home.com> wrote in message
    news:3a74de01$1@news.devx.com...
    >


    > What I'm trying to do is get away from having to have an Excel spreadsheet
    > open, i.e., where the user can input data directly into the spreadsheet.
    > By writing the interface in VB (and mind you, this IS the first time I've
    > done this), I can store the data in the spreadsheet without ever having to
    > even start Excel.


    This might seem like nitpicking, but Excel will have to be started. As an AX
    EXE, Excel executes as an AX EXE server. The following code will make it
    visible on screen:

    Dim oExcel as Excel.Application

    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    Msgbox "I see you."
    oExcel.Quit

    The Msgbox is there to stop the code. Quit is only needed if you load or
    create workbooks.


    But for troubleshooting purposes, I have opened the spreadsheet
    > in a window on one side of my screen and watched the changes occur in it
    > at the same time I'm watching the code as I step through it (F8). I hope
    > all that makes sense.

    OK, here's the deal.

    Select and Activate methods of the Range object only work if the parent
    worksheet is active and visible.

    So this code will fail because the second sheet isn't active:
    oWB.Sheets(1).Select
    oWB.Sheets(2).Range("a1").select


    This code will fail because the sheet was hidden:
    With oWB.Sheets(1)
    .Visible = xlHidden
    .Range("A1").Select
    End with

    Check your code and make sure both of these conditions are correct at the
    time of the Range.Select statement.

    I would recommend you add either a conditional compile code or a global
    boolean (triggered off a command line switch) and only do the select
    statements inside the conditional compile.

    if gbDevMode Then
    oWS.Select
    oWS.Range("a1").Select
    end if

    Two other notes:
    1. Putting this code in an EXE will cause a performance hit since the data
    will be marshalled between process spaces. You might consider writting a DLL
    and calling it from Excel.
    2. Pay close attention to the object types returned by Excel's collections.
    Some of these collections return generic objects (the sheets collection is
    one) which should be casted to a more specific object type inorder to be
    early bound.

    --
    ~~~
    !ti timda I ,KO
    ..em deppals nocaeB sivaM
    !draH
    ~~
    C'Ya,
    mrfelis@yahoo!com
    Dale Boyer <boyers6@home.com> wrote in message
    news:3a74de01$1@news.devx.com...
    >
    > I had accidently posted this to the wrong forum, but since you're

    attempted
    > to help me (and I DO appreciate any help I can get), I'll try to make it
    > clearer...
    >
    > What I'm trying to do is get away from having to have an Excel spreadsheet
    > open, i.e., where the user can input data directly into the spreadsheet.
    > By writing the interface in VB (and mind you, this IS the first time I've
    > done this), I can store the data in the spreadsheet without ever having to
    > even start Excel. But for troubleshooting purposes, I have opened the

    spreadsheet
    > in a window on one side of my screen and watched the changes occur in it
    > at the same time I'm watching the code as I step through it (F8). I hope
    > all that makes sense.
    >
    > Anyway, as I step through it, one line at a time, I can watch (as this

    segment
    > of code runs) the range A1:F56 copy from one sheet, and then paste to the
    > other sheet, exactly as it is intended to do.
    >
    > But if I close out Excel, and THEN run the program, that's when I get the
    > error. Everything else about the program works fine, except for that one
    > line. The data is taken from the user input screen and placed in to the

    proper
    > location in the spreadsheet (confirmed by opening it later and looking at
    > the appropriate sheets), the calculations are performed on the data, etc.,
    > etc. But when it gets to that line of code, THAT'S when I get the error.
    >
    > I'll try removing the 3rd line and see if that makes a difference.

    Although
    > I might have already tried that... I've tried several different things,

    including
    > ACTIVATE. In the meantime, I hope I've made a little more sense out of

    this.
    > Let me know if you've got any ideas.
    >
    > Thanks in advance,
    >
    > Dale
    >
    > "mrfelis" <mrfelis@yahoo.NOSPAM.com> wrote:
    > >Dale Boyer <dale.boyer@faa.gov> wrote in message
    > >news:3a71ce82@news.devx.com...
    > >>
    > >> TimeSheet.Range("A1:F56").Copy
    > >> LastSheet.Select
    > >> LastSheet.Range("A1").Select
    > >> LastSheet.Range("A1").PasteSpecial

    > >
    > >The two selects's are only required to see what is going on.
    > >
    > >> This code WORKS. . .
    > >>
    > >> . . IF I have the spreadsheet open at the same time, the code runs

    > >without
    > >> error. HOWEVER, if the spreadsheet is closed (and mind you, everything

    > >else
    > >> STILL works okay), I get the following:

    > >
    > >What do you mean by closed?
    > >
    > >

    >




  5. #5
    mrfelis Guest

    Re: VB and an Excel Spreadsheet

    Dale Boyer <dale.boyer@faa.gov> wrote in message
    news:3a71ce82@news.devx.com...
    >
    > TimeSheet.Range("A1:F56").Copy
    > LastSheet.Select
    > LastSheet.Range("A1").Select
    > LastSheet.Range("A1").PasteSpecial


    The two selects's are only required to see what is going on.

    > This code WORKS. . .
    >
    > . . IF I have the spreadsheet open at the same time, the code runs

    without
    > error. HOWEVER, if the spreadsheet is closed (and mind you, everything

    else
    > STILL works okay), I get the following:


    What do you mean by closed?



  6. #6
    Dale Boyer Guest

    Re: VB and an Excel Spreadsheet


    I had accidently posted this to the wrong forum, but since you're attempted
    to help me (and I DO appreciate any help I can get), I'll try to make it
    clearer...

    What I'm trying to do is get away from having to have an Excel spreadsheet
    open, i.e., where the user can input data directly into the spreadsheet.
    By writing the interface in VB (and mind you, this IS the first time I've
    done this), I can store the data in the spreadsheet without ever having to
    even start Excel. But for troubleshooting purposes, I have opened the spreadsheet
    in a window on one side of my screen and watched the changes occur in it
    at the same time I'm watching the code as I step through it (F8). I hope
    all that makes sense.

    Anyway, as I step through it, one line at a time, I can watch (as this segment
    of code runs) the range A1:F56 copy from one sheet, and then paste to the
    other sheet, exactly as it is intended to do.

    But if I close out Excel, and THEN run the program, that's when I get the
    error. Everything else about the program works fine, except for that one
    line. The data is taken from the user input screen and placed in to the proper
    location in the spreadsheet (confirmed by opening it later and looking at
    the appropriate sheets), the calculations are performed on the data, etc.,
    etc. But when it gets to that line of code, THAT'S when I get the error.

    I'll try removing the 3rd line and see if that makes a difference. Although
    I might have already tried that... I've tried several different things, including
    ACTIVATE. In the meantime, I hope I've made a little more sense out of this.
    Let me know if you've got any ideas.

    Thanks in advance,

    Dale

    "mrfelis" <mrfelis@yahoo.NOSPAM.com> wrote:
    >Dale Boyer <dale.boyer@faa.gov> wrote in message
    >news:3a71ce82@news.devx.com...
    >>
    >> TimeSheet.Range("A1:F56").Copy
    >> LastSheet.Select
    >> LastSheet.Range("A1").Select
    >> LastSheet.Range("A1").PasteSpecial

    >
    >The two selects's are only required to see what is going on.
    >
    >> This code WORKS. . .
    >>
    >> . . IF I have the spreadsheet open at the same time, the code runs

    >without
    >> error. HOWEVER, if the spreadsheet is closed (and mind you, everything

    >else
    >> STILL works okay), I get the following:

    >
    >What do you mean by closed?
    >
    >



  7. #7
    mrfelis Guest

    Re: VB and an Excel Spreadsheet

    Dale Boyer <boyers6@home.com> wrote in message
    news:3a74de01$1@news.devx.com...
    >


    > What I'm trying to do is get away from having to have an Excel spreadsheet
    > open, i.e., where the user can input data directly into the spreadsheet.
    > By writing the interface in VB (and mind you, this IS the first time I've
    > done this), I can store the data in the spreadsheet without ever having to
    > even start Excel.


    This might seem like nitpicking, but Excel will have to be started. As an AX
    EXE, Excel executes as an AX EXE server. The following code will make it
    visible on screen:

    Dim oExcel as Excel.Application

    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    Msgbox "I see you."
    oExcel.Quit

    The Msgbox is there to stop the code. Quit is only needed if you load or
    create workbooks.


    But for troubleshooting purposes, I have opened the spreadsheet
    > in a window on one side of my screen and watched the changes occur in it
    > at the same time I'm watching the code as I step through it (F8). I hope
    > all that makes sense.

    OK, here's the deal.

    Select and Activate methods of the Range object only work if the parent
    worksheet is active and visible.

    So this code will fail because the second sheet isn't active:
    oWB.Sheets(1).Select
    oWB.Sheets(2).Range("a1").select


    This code will fail because the sheet was hidden:
    With oWB.Sheets(1)
    .Visible = xlHidden
    .Range("A1").Select
    End with

    Check your code and make sure both of these conditions are correct at the
    time of the Range.Select statement.

    I would recommend you add either a conditional compile code or a global
    boolean (triggered off a command line switch) and only do the select
    statements inside the conditional compile.

    if gbDevMode Then
    oWS.Select
    oWS.Range("a1").Select
    end if

    Two other notes:
    1. Putting this code in an EXE will cause a performance hit since the data
    will be marshalled between process spaces. You might consider writting a DLL
    and calling it from Excel.
    2. Pay close attention to the object types returned by Excel's collections.
    Some of these collections return generic objects (the sheets collection is
    one) which should be casted to a more specific object type inorder to be
    early bound.

    --
    ~~~
    !ti timda I ,KO
    ..em deppals nocaeB sivaM
    !draH
    ~~
    C'Ya,
    mrfelis@yahoo!com
    Dale Boyer <boyers6@home.com> wrote in message
    news:3a74de01$1@news.devx.com...
    >
    > I had accidently posted this to the wrong forum, but since you're

    attempted
    > to help me (and I DO appreciate any help I can get), I'll try to make it
    > clearer...
    >
    > What I'm trying to do is get away from having to have an Excel spreadsheet
    > open, i.e., where the user can input data directly into the spreadsheet.
    > By writing the interface in VB (and mind you, this IS the first time I've
    > done this), I can store the data in the spreadsheet without ever having to
    > even start Excel. But for troubleshooting purposes, I have opened the

    spreadsheet
    > in a window on one side of my screen and watched the changes occur in it
    > at the same time I'm watching the code as I step through it (F8). I hope
    > all that makes sense.
    >
    > Anyway, as I step through it, one line at a time, I can watch (as this

    segment
    > of code runs) the range A1:F56 copy from one sheet, and then paste to the
    > other sheet, exactly as it is intended to do.
    >
    > But if I close out Excel, and THEN run the program, that's when I get the
    > error. Everything else about the program works fine, except for that one
    > line. The data is taken from the user input screen and placed in to the

    proper
    > location in the spreadsheet (confirmed by opening it later and looking at
    > the appropriate sheets), the calculations are performed on the data, etc.,
    > etc. But when it gets to that line of code, THAT'S when I get the error.
    >
    > I'll try removing the 3rd line and see if that makes a difference.

    Although
    > I might have already tried that... I've tried several different things,

    including
    > ACTIVATE. In the meantime, I hope I've made a little more sense out of

    this.
    > Let me know if you've got any ideas.
    >
    > Thanks in advance,
    >
    > Dale
    >
    > "mrfelis" <mrfelis@yahoo.NOSPAM.com> wrote:
    > >Dale Boyer <dale.boyer@faa.gov> wrote in message
    > >news:3a71ce82@news.devx.com...
    > >>
    > >> TimeSheet.Range("A1:F56").Copy
    > >> LastSheet.Select
    > >> LastSheet.Range("A1").Select
    > >> LastSheet.Range("A1").PasteSpecial

    > >
    > >The two selects's are only required to see what is going on.
    > >
    > >> This code WORKS. . .
    > >>
    > >> . . IF I have the spreadsheet open at the same time, the code runs

    > >without
    > >> error. HOWEVER, if the spreadsheet is closed (and mind you, everything

    > >else
    > >> STILL works okay), I get the following:

    > >
    > >What do you mean by closed?
    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center