Solved

VBA - How to manipulate a workbook/sheet after opening

Posted on 2008-09-30
4
880 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Simple Linear Regression
Introduction to Processes

631 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