Picture on Excel sheet 1

[MichM3]

Dear,

I'm not a VBA expert and I'm struggling with a detail (I assume) in my code.
From Business Objects I wrote a VBA script that will open an Excel Workbook and place a Picture Top-Left on each sheet of the Workbook. It works fine for 1 WB, but not for the loop of a bunch of WB's. My code misses some key-code I guess in order to operate robust and correct.

Here is the code:

Private Sub ExcelInsertPicture(ByVal noOfSheets As Integer, ByVal DocPath As String, ByVal DocName As String)

' Define Excel attributes
Dim xlApp As Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet
Dim k As Integer
' Path to Picture
Dim pPath As String
pPath = "<yourCompanyLogoPath>"

Set xlApp = New Excel.Application

With xlApp

'Open Workbook
Set xlWB = .Workbooks.Open(DocPath & DocName & ".xls", ReadOnly:=False, Editable:=True)

'Loop through all Sheets
For k = 1 To noOfSheets

'Activate appropriate Sheet
Set xlWS = xlWB.Worksheets(k)
xlWS.Activate

'Insert picture in Sheet
xlWS.Pictures.Insert (pPath)

'Align picture Top-Left
With xlWS.Pictures
.Left = Range("A1").Left
.Top = Range("A1").Top
End With

Next k

End With

'Activate 1st Sheet before saving
xlWB.Worksheets(1).Activate

'Save and Close Workbook
xlWB.Save
xlWB.Close

Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing

End Sub


I always receive the error: "method range of object _global failed"

the Error occurs on line:

.Left = Range("A1").Left

What am I doing wrong? How could I enhance my code?

 

[SkipVought]

Hi,

You must reference the Range to the sheet...

    With xlApp
    
        'Open Workbook
        Set xlWB = .Workbooks.Open(DocPath & DocName & ".xls", ReadOnly:=False, Editable:=True)
        
        'Loop through all Sheets
        For k = 1 To noOfSheets
        
            Set xlWS = xlWB.Worksheets(k)
            
            'Align picture Top-Left
            With xlWS.Pictures.Insert(pPath)
                .Left = [b]xlWS.[/b]Range("A1").Left
                .Top = [b]xlWS.[/b]Range("A1").Top
            End With
        Next k
    
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MichM3]

thanks man!!! works fine now