Monday, March 17, 2014

Excel Get Used Range Count and Insert Picture using VB Scripting

Set objXL = CreateObject(“Excel.Application”)
objXL.visible = True
objXL.DisplayAlerts = false
Set wkb = objXL.Workbooks.Open(ExcelFilePath)
Set ws = wkb.Sheets(“Sample”)
Used_RowCount =  ws.UsedRange.Rows.Count
Used_ColumnCount =  ws.UsedRange.Rows.Count
Next_Row_Range = Used_RowCount +3
Add_Column_Range = Used_ColumnCount + 10
Cell_Range = “A” & Next_Row_Range &”:G” & Add_Column_Range
Set objRng = ws.Range(Cell_Range)
‘Insert Picture..
objRng.Merge
Set Picture = ws.Pictures.Insert(ImageFilePath)
With Picture
.Top = objRng.Top
.Left = objRng.Left
.Width = objRng.Width
.Height = objRng.Height
End With
‘Insert text …
ws.Cells(Cell_Range).Value = “Test value”
wkb.SaveAs ExcelFilePath
wkb.Close
objXL.Quit
‘********************************************************
Function FindLastCell()
Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = ActiveSheet.UsedRange.Columns.Count
mRow = 0
For i = 1 To lCol
lRow = Range(Cells(rows.count, i), Cells(rows.count, i)).End(xlUp).Row
If lRow > mRow Then
mRow = lRow
mCol = i
Else
End If
Next i
FindLastCell = Range(Cells(mRow, mCol), Cells(mRow, mCol)).Address
End Function

No comments:

Post a Comment