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(m
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")
Set tempWorkbook = ExcelSheet.Workbooks.Open(pattern)
Set tempSheet = tempWorkbook.Worksheets(myPattern) 'error!!!!!!!!