topic RE: Excel Autofill VBO in Product Forum

Excel Autofill VBO

PaulWiatroski — Wed, 11 Apr 2018 00:41:00 GMT

Hi, Has anyone created an Excel Autofill VBO? I've been playing around trying to create one, but not having much luck getting it to work. If someone has done this and can share the code, that would be great. Thanks, Paul

I was able to get my…

PaulWiatroski — Wed, 11 Apr 2018 18:24:00 GMT

I was able to get my Autofill VBO to work. SourceRange and FillRange are passed in to the VBO. See code below: Dim wb, ws, source_range, fill_range As Object Dim excel, sheet, range As Object Try wb = GetWorkbook(Handle, Workbook) ws = GetWorksheet(Handle, Workbook, Worksheet) wb.Activate() ws.Activate() excel = ws.Application sheet = excel.ActiveSheet source_range = sheet.Range(SourceRange) fill_range = sheet.Range(FillRange) source_range.AutoFill(Destination:=fill_range) Success = True Catch e As Exception Success = False Message = e.Message Finally wb = Nothing ws = Nothing excel = Nothing sheet = Nothing range = Nothing End Try

RE: I was able to get my…

nadiim — Mon, 07 Oct 2019 14:55:00 GMT

This code is going to work only if the SourceRange is selected on the Excel sheet, otherwise it will give an error.
Add the 1 line code in bold to get it working and make sure there are values in the excel sheet.

source_range = sheet.Range(SourceRange)
source_range.Select()
fill_range = sheet.Range(FillRange)

------------------------------
Nadiim Peerbocus
Assistant Manager
EY
Africa/Dar_es_Salaam
------------------------------

RE: Excel Autofill VBO

AndreyKudinov — Thu, 10 Oct 2019 07:54:00 GMT

Paste this as new action (tab) in Excel VBO:
https://pastebin.com/x0gg5dK9

Doesn't need anything to be selected etc, just give it an instance, workbook, worksheet, source range, optionally destination (uses CurrentRegion when blank), optionally xlAutoFillType

Edit to clarify: Not using original code, my own solution.
My point is that you don't need to activate anything, this should work just fine:

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)
ws.Range(SourceRange).AutoFill(Destination:=ws.Range(FillRange))

My version has extra options:
- worksheet index instead of name (optional, either will do)
- ActiveSheet is used, when worksheet name and index are not set.
- blank dstRange, then it tries to guess it with CurrentRegion, which is not exactly what Excel does internally, but close and good enough for most cases
- xlAutoFillType, because sometimes xlFillDefault is not what you want.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Excel Autofill VBO

TomasKlimas1 — Tue, 27 Oct 2020 12:46:00 GMT

Thanks Andrey, I will try your solution

RE: Excel Autofill VBO

ritansh.jatwani — Tue, 27 Oct 2020 15:36:00 GMT

Hi Paul,

Please refer the below video for achieving the functionality:

https://www.youtube.com/watch?v=b_H-Zy_TJ4M

Hope it works for you.

------------------------------
Ritansh Jatwani
Consultant
EY
Asia/Kolkata
------------------------------

RE: Excel Autofill VBO

AndreyKudinov — Tue, 27 Oct 2020 23:50:00 GMT

@ritansh.jatwani, Excel Autofill does more than just repeating same cell value until the end of the table, look: https://www.excel-easy.com/examples/autofill.html

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Excel Autofill VBO

ritansh.jatwani — Thu, 29 Oct 2020 11:02:00 GMT

@AndreyKudinov- True, if we want to achieve the functionality as mentioned in https://www.excel-easy.com/examples/autofill.html, then your code will work perfectly fine.

------------------------------
Ritansh Jatwani
Consultant
EY
Asia/Kolkata
------------------------------