Open multiple excel spreadsheet from ppt VBA

  • Thread starter Thread starter Lily
  • Start date Start date
L

Lily

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,
 
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,
Click to expand...

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/
 
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")
Click to expand...
set wksheet2=XLApplication.workbooks.open(excel filename as
string).worksheet("ws2")
Click to expand...

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,
 
this actually don't work.

thanks,

Steve Rindsberg said:
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
================================================
Click to expand...
 
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


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,
Click to expand...

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
===========
 
Back
Top