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

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?

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?

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?
>
>

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?
>
>

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?
> >
> >
>

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?
> >
> >
>