CODEWITHSUNDEEP
ms-accessexcelms-access-2007vba
KSM
KSM

Reputation: 262

How to reference a Excel Worksheet within a function

I am trying to call a excel work sheet to a function however i get an error 438 "OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD"

The code below makes the call to applyStyle1

Dim wkb7 As Excel.Workbook
Set wkb7 = Excel.Application.Workbooks.Open(strDir&"\NEXTDAY.xls")
wkb7.ActiveSheet.Cells.Select
Selection.Copy
Set wks7 = wkb.Sheets.Add

applyStyle1 (wks7)

The function: 

Function applyStyle1(wksContainer As Excel.Worksheet)
     With wksContainer
              ......
     End With
End Function

This code works when the function call is replaced with the code within the function, however since it is called multiple times i require it to be in a function rather duplicating the code. Thank you for your time, pleas advise me if you require a more detailed explanation.

Upvotes: 2

Views: 187

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

First Problem

The line strDir&"\NEXTDAY.xls" should be strDir & "\NEXTDAY.xls". There should be a space before and after the & sign.

Second Problem

You have declared wkb7 but are using wkb in Set wks7 = wkb.Sheets.Add. I would recommend using Option Explicit

Third Problem

Change the line as suggested by @MarkHone Set wks7 = wkb.Sheets.Add to

Set wks7 = wkb7.Worksheets.Add

Fourth Problem (Your Actual Problem!)

applyStyle1 is a function which needs to return something. If you are not returning anything then Use a Sub instead.

For example

Sub Sample()
    Dim wkb7 As Excel.Workbook
    Dim wks7 As Excel.Worksheet

    Set wkb7 = Excel.Application.Workbooks.Open(strDir & "\NEXTDAY.xls")
    wkb7.ActiveSheet.Cells.Copy

    Set wks7 = wkb7.Worksheets.Add

    applyStyle1 wks7
End Sub

Sub applyStyle1(wksContainer As Excel.Worksheet)
    With wksContainer

    End With
End Sub

Upvotes: 5

MarkHone
MarkHone

Reputation: 381

Please try changing your 5th line from:

Set wks7 = wkb.Sheets.Add

to:

Set wks7 = wkb.Worksheets.Add

Sheets will include all sheets within the workbook, including chart sheets.

Upvotes: 3

Related Questions