Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA - How to manipulate a workbook/sheet after opening

Posted on 2008-09-30
4
Medium Priority
?
898 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 375 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Progress
Loops Section Overview

886 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