Open multiple excel spreadsheet from ppt VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lily - 08 May 2008 20:44 GMT

I used

set wksheet1=XLApplication.workbooks.open(excel filename as
string).worksheet("ws1")

and it did work.

But I don't know how to open another worksheet from the same excel file.

thanks,

Reply to this Message

Steve Rindsberg - 08 May 2008 22:03 GMT

> I used
>
[quoted text clipped - 4 lines]
>
> But I don't know how to open another worksheet from the same excel file.

Something like this, maybe:

Set WkBook = XLApplication.workbooks.open(excel filename as string)

Set WkSheet1 = WkBook.Worksheets("ws1")
Set WkSheet2 = WkBook.Worksheets("ws2")

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

Reply to this Message

Lily - 08 May 2008 23:15 GMT

this actually don't work.

thanks,

> > I used
> >
[quoted text clipped - 18 lines]
> PPTools: www.pptools.com
> ================================================

Reply to this Message

Steve Rindsberg - 09 May 2008 16:44 GMT

> this actually don't work.

w/o more detailed information than you've provided, it's hard for anyone to
help.

Please give the exact text of the error messages, what line of code they
occurred on and include a bit more of the surrounding code.

> thanks,
>
[quoted text clipped - 20 lines]
> > PPTools: www.pptools.com
> > ================================================

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

Reply to this Message

T Lavedas - 08 May 2008 22:23 GMT

> I used
>
[quoted text clipped - 6 lines]
>
> thanks,

Here this worked for me ...

with createobject("excel.application")
set oWB1 = .workbooks.open(sXLfilename)'
oWB1.worksheets(1).activate
.visible = true
end with

msgbox "pause"

with createobject("excel.application")
set oWB2 = .workbooks.open(sXLfilename)'
oWB2.worksheets(2).activate
.visible = true
end with

Entirely different instatiations of the Excel.Application object are
needed to get simultaneous displays of different sheets in a
workbook. Only the first one you open will be editable. Later
versions are opened Read-only. Also, they will appear one over the
other (the second will hide the first, until it is moved, etc.). I
didn't take the time to figure out how to move them about.

HTH,

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

Reply to this Message

Lily - 08 May 2008 23:14 GMT

What I had before is essentially similar to your method. I had the following:
set wksheet1=XLApplication.workbooks.open(excel filename as

> string).worksheet("ws1")

set wksheet2=XLApplication.workbooks.open(excel filename as

> string).worksheet("ws2")

But at the second line, I got a type mismatch error, which I don't
understand why.

with you method, how can you refer to individual cells in the worksheet, and
especially, if I am using a loop to go over a series of cells?

thanks,

> > I used
> >
[quoted text clipped - 35 lines]
> ===========
> http://members.cox.net/tglbatch/wsh/

Reply to this Message

T Lavedas - 09 May 2008 15:21 GMT

> What I had before is essentially similar to your method. I had the following:
> set wksheet1=XLApplication.workbooks.open(excel filename as> string).worksheet("ws1")
[quoted text clipped - 10 lines]
>
> thanks,

First, You have a typo in your posting in that the .worksheet("ws1")
part must be .worksheetS("ws1") (with an S). Does that indicate
anything to you? Specifically, that the property is a COLLECTION of
all of the worksheets in the workbook. So, you could do something
like this ...

set colWksheets=XLApplication.workbooks.open(excel filename as string)

set wks1 = colWksheets("ws1")
set wks2 = colWksheets("ws2")

There are may ways to access cells in a worksheet. A most common way
is to use the Range() property, such as ...

contentWS1A1 = wks1.Range("A1").Value
contentWS2A1 = wks2.Range("A1").Value

Another is the Cells() property ...

contentWS1A1 = wks1.Range(0,0).Value
contentWS1B2 = wks2.Range(1,1).Value

where the parameters are row, col offsets.

Maybe you want to open Excel and record a few macros and then look at
the code that the program creates for the macro to get an idea of
Excel's object model. You can also look at the Excel VBA help
documentation.

Tom Lavedas
===========

Reply to this Message

Rate this thread: