Solved

VBA - How to manipulate a workbook/sheet after opening

Posted on 2008-09-30
4
871 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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?
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

808 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