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

open specific Excel Worksheet name

I need to open a particular sheet in an excel workbook.  I tried:
    Set wbk = appExcel.Workbooks.Open(strImportFile)
    Set wks = wbk.Worksheets(rstImportSpec!SheetName)
where SheetName = 'Main Page' but I get a 'Type Mismatch' error
However  Set wks = wbk.Worksheets(1) works perfectly.  The problem is sometimes the sheets are not all the same in all workbooks but the particular Sheet Name always exists

what am I doing wrong?

Michael
0
UniqueData
Asked:
UniqueData
1 Solution
 
TextReportCommented:
try

Set wks = wbk.Sheets(rstImportSpec!SheetName)

Cheers, Andrew
0
 
Malik1947Commented:
use this to access a specific sheet

Sheet name is a string.


Set wks = wbk.Sheets(SheetName)

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
dim xlObj as object, xlSht as object
set xlObj=createobject("excel.application")
      xlObj.workbooks.open(strImportFile)

set xlSht=xlobj.activeworkbook.worksheets(rstImportSpec("Sheetname"))
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
MikeTooleCommented:
It should work, make sure that the names are the same:

    Set wbk = appExcel.Workbooks.Open(strImportFile)
Debug.Print wkb.Worksheets(1).name,  rstImportSpec!SheetName
stop
    Set wks = wbk.Worksheets(rstImportSpec!SheetName)
0
 
Rey Obrero (Capricorn1)Commented:
slight correction

dim xlObj as object, xlSht as object
set xlObj=createobject("excel.application")
      xlObj.workbooks.open(strImportFile)

set xlSht=xlobj.activeworkbook.worksheets(cstr(rstImportSpec("Sheetname")))
0
 
Rey Obrero (Capricorn1)Commented:
or in your code

Set wks = wbk.Worksheets(cstr(rstImportSpec!SheetName))
0
 
UniqueDataAuthor Commented:
Sheets did not work.  It looks like the trick was cstr as cap suggested.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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