0
votes

I am writing VB script to insert the images from directory to Excel file with caption to images.

While I am running below code I am getting the error :


Error

Script: C:\Users\user\Desktop\asd.vbs

Line: 7

Char: 13

Error: Type mismatch: 'range'

Code: 800A000D

Source: Microsoft VBScript runtime error

Code :-

set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
set objWorkbook=objExcel.workbooks.add()
With objExcel.ActiveSheet.Pictures.insert("C:\Users\user\Desktop\test\IMG_9717.JPG")
        .Left = range("A").Left
        .Top = range("A").Top
        .Height = range("A").RowHeight
        .Placement = xlMoveAndSize
End With
objWorkbook.Saveas "c:\testXLS1.xls"
objWorkbook.Close
objExcel.workbooks.close
objExcel.quit
set objExcel = nothing

Please guide me on above error. How can I fix this ?

4
Do you need to specify a complete cell reference e.g. A1? - QHarr

4 Answers

1
votes

VBScript doesn't know what Range means. If this was VBA, then it would refer directly to the ActiveSheet of the Application object.

You would need to qualify it properly with the application and sheet references (and as others have said, provide a full cell reference as well):

objExcel.ActiveSheet.Range("A1").Left

...etc

0
votes

I would suggest trying for a complete cell reference rather than just the column e.g.

Range("A1").RowHeight
0
votes

Replace:

Range("A")

with:

Range("A1")

there may be other errors

0
votes

I tried below code. Its working fine. While using the range() function in VBS, we have to use it with Active Workbook. Check the below code.

set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
set objWorkbook=objExcel.workbooks.add()

set objsheet=objWorkbook.Worksheets.Add

Set objPic = objExcel.ActiveSheet.Pictures.insert("C:\Users\user\Desktop\test\IMG_9717.JPG")

With objPic
        .Left = objsheet.range("D3").Left
        .Top = objsheet.range("D3").Top
        .Height = objsheet.range("D3").RowHeight
End With

objWorkbook.Saveas "c:\testXLS1.xls"
objWorkbook.Close
objExcel.workbooks.close
objExcel.quit
set objExcel = nothing