open specific Excel Worksheet name
I need to open a particular sheet in an excel workbook. I tried:
Set wbk = appExcel.Workbooks.Open(st
Set wks = wbk.Worksheets(rstImportSp
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
Set wbk = appExcel.Workbooks.Open(st
Set wks = wbk.Worksheets(rstImportSp
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
use this to access a specific sheet
Sheet name is a string.
Sheet name is a string.
Set wks = wbk.Sheets(SheetName)
dim xlObj as object, xlSht as object
set xlObj=createobject("excel.
xlObj.workbooks.open(strIm
set xlSht=xlobj.activeworkbook
set xlObj=createobject("excel.
xlObj.workbooks.open(strIm
set xlSht=xlobj.activeworkbook
It should work, make sure that the names are the same:
Set wbk = appExcel.Workbooks.Open(st
Debug.Print wkb.Worksheets(1).name, rstImportSpec!SheetName
stop
Set wks = wbk.Worksheets(rstImportSp
Set wbk = appExcel.Workbooks.Open(st
Debug.Print wkb.Worksheets(1).name, rstImportSpec!SheetName
stop
Set wks = wbk.Worksheets(rstImportSp
slight correction
dim xlObj as object, xlSht as object
set xlObj=createobject("excel.
xlObj.workbooks.open(strIm
set xlSht=xlobj.activeworkbook
dim xlObj as object, xlSht as object
set xlObj=createobject("excel.
xlObj.workbooks.open(strIm
set xlSht=xlobj.activeworkbook
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sheets did not work. It looks like the trick was cstr as cap suggested.
Set wks = wbk.Sheets(rstImportSpec!S
Cheers, Andrew