Solved

VBA - How to manipulate a workbook/sheet after opening

Posted on 2008-09-30
4
872 Views
Last Modified: 2013-11-10
Hi guys,

OK, I've pieced together enough VBA code to make my program work except for one part. What I'm looking to do is search for reports generated in a date range (works), then open the spreadsheets that match the criteria and transfer the cell values into an array via a nested loop, then transfer the array into a final spreadsheet using a range. I can open the reports, but the below code generates, 'class does not support automation' at            Set tempSheet = tempWorkbooks.Worksheets(myPattern)
It doesn't look as if I can name the worksheet to be able to manipulate it (can I?). If not, any other ideas on how to refer to it? I've tried many different methods that I found on the web, but each one generates a different error... it seems that VBA does not support many of the functions of the other VB flavors.  I will be extremely grateful if anyone could help me out or point me in the right direction. Thanks a ton!

Dim ExcelSheet As Object
 
Dim tempWorkbook = Excel.Workbook
 
Dim tempSheet = Excel.Worksheet
    
Set ExcelSheet = CreateObject("Excel.Application")
                 
With ExcelSheet
 
Set tempWorkbook = ExcelSheet.Workbooks.Open(pattern)
 
Set tempSheet = tempWorkbook.Worksheets(myPattern)              'error!!!!!!!!
 
tempSheet.Select

Open in new window

0
Comment
Question by:patricio26
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
dmang earned 125 total points
ID: 22607358
hi patricio26
You can try a couple of things to locate a worksheet
1.  Loop thru the worksheets in a workbook looking for a specific sheet name:

Dim ExcelSheet As Object
Dim tempWorkbook = Excel.Workbook
Dim tempSheet = Excel.Worksheet
dim w as integer
Set ExcelSheet = CreateObject("Excel.Application")                
With ExcelSheet
Set tempWorkbook = ExcelSheet.Workbooks.Open(excelfilename)
for w = 1 to tempworkbook.worksheets.count
    if tempworkbook.worksheets(w).name = "specificsheetname" then
        exit for
   end if
next w
if w > tempworkbook.worksheets.count then     'not found
    exit sub
end if
Set tempSheet = tempWorkbook.Worksheets(myPattern)  
tempsheet.activate
....

2.  Look for some cell content on a worksheet that indicates it's the one you want.

for w = 1 to tempworkbook.worksheets.count
    if tempworkbook.worksheets(w).cells(1,1) = "Sales Territory" then
       if tempworkboo.worksheets(w).cells(1,2)  = "Sales Agent" then
         exit for
   end if
next w
if w > tempworkbook.worksheets.count then     'not found
    exit sub
end if
Set tempSheet = tempWorkbook.Worksheets(myPattern)  
tempsheet.activate

....
Regards
DMang
0
 

Author Closing Comment

by:patricio26
ID: 31501626
For some reason, when I used worksheets(1) (used 1 because there is only one worksheet per report), it worked, even though the worksheet name is the same as the patter string.  Many thanks! :)
0
 
LVL 12

Expert Comment

by:kgerb
ID: 22607639
I'm assuming "mypattern" is a string variable representing the name of the worksheet into which you wish to copy the data.  Correct?  If a worksheet with this name does not already exist you must first either create a new sheet and name it "mypattern" or rename one of the existing worksheets "mypattern".

To create a new worksheet and rename it use

With tempworkbook.Sheets.Add
    .Name = mypattern
End With

To change the name of an existing worksheet use

tempworkbook.sheets("Existing Name") = mypattern

Does this help or am I misunderstanding what you need?

Kyle
0
 
LVL 12

Expert Comment

by:kgerb
ID: 22607651
oops, sorry
by the time I posted this you had already accepted dmang's post
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question