• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 901
  • Last Modified:

VBA - How to manipulate a workbook/sheet after opening

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
patricio26
Asked:
patricio26
  • 2
1 Solution
 
dmangCommented:
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
 
patricio26Author Commented:
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
 
kgerbChief EngineerCommented:
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
 
kgerbChief EngineerCommented:
oops, sorry
by the time I posted this you had already accepted dmang's post
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now