A «hidden» object that allows to access objects without prefixing them with application in VBA code (for example cells.… instead of application.cells.…).
A Workbook object is the object that corresponds to an Excel file (*.xlsm, *.xlsx, etc.). The currently active workbook is obtained with application.activeWorkbook.
A WorkbookConnection objects stores the information that is required to obtain data from an external data source (that is not located in the Workbook itself.)
'
' ..\..\runVBAFilesInOffice.vbs -excel randBetween -c Go
'
' Note, the function returns an integer.
'
' Compare with -> rand()
sub Go()
range("a1").formula = "=randBetween(100, 110)"
range("a2").formula = "=randBetween(100, 110)"
range("a3").formula = "=randBetween(100, 110)"
end sub
'
' ..\..\runVBAFilesInOffice.vbs -excel if -c Go
'
sub Go()
range("a1:b10").formula = "=rand()"
' Note: «a1<b1» dynamically changes with the rows in which
' they appear.
range("c1:c10").formula = "=if( a1<b1 , ""less"" , ""greater or equal"")"
end sub
'
' ..\..\runVBAFilesInOffice.vbs -excel formulaR1C1 -c Go
'
sub Go()
range("b2").formula = "=rand()"
range("c2").formula = "=rand()"
' Note, the formula turns into
' « =IF( B2 < C2; "less"; "greater or equal" ) »
' in the produced formula
range("d2").formulaR1C1 = "=if( rc[-2] < rc[-1] , ""less"" , ""greater or equal"" )"
end sub
'
' ..\..\..\runVBAFilesInOffice.vbs -excel example_01 -c Go %CD%
'
Sub Go(cur_working_dir as string)
dim pivot_sheet as workSheet
dim pivot_cache as pivotCache
dim pivot_table as pivotTable
dim pivot_table_upper_left as range
dim pf_col_1 as pivotField
dim pf_col_2 as pivotField
'
call importCSV(cur_working_dir & "\pivot.csv", activeSheet, range("$a$1"), "csv_data")
'
set pivot_sheet = sheets.add
set pivot_cache = activeWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "csv_data", Version:=xlPivotTableVersion14)
set pivot_table_upper_left = pivot_sheet.range("C3")
set pivot_table = pivot_cache.CreatePivotTable ( TableDestination:= pivot_table_upper_left )
set pf_col_1 = pivot_table.pivotFields("col_1")
set pf_col_2 = pivot_table.pivotFields("col_2")
pf_col_1.orientation = xlRowField
pf_col_2.orientation = xlColumnField
call pivot_table.addDataField (pf_col_2, "Count of col_2", xlCount)
activeWorkbook.saved = true
End Sub
private sub importCSV(csv_file_name as string, sheet_ as workSheet, range_ as range, name_ as string) ' {
'
' -> https://github.com/ReneNyffenegger/runVBAFilesInOffice/blob/master/Excel/ObjectModel/QueryTable/load_CSV.bas
'
With ActiveSheet.QueryTables.Add( _
Connection:= "TEXT;" & csv_file_name , _
Destination:=range_)
.name = name_
.fieldNames = True
.rowNumbers = False
.preserveFormatting = True
.textFilePlatform = 437
.textFileStartRow = 1
.textFileParseType = xlDelimited
.textFileTextQualifier = xlTextQualifierDoubleQuote
.textFileConsecutiveDelimiter = False
.textFileCommaDelimiter = True
.textFileTrailingMinusNumbers = True
.refresh BackgroundQuery:=False
end with
end sub
'
' ..\..\..\runVBAFilesInOffice.vbs -excel example_02 -c Go %CD%
'
Sub Go(cur_working_dir as string)
dim pivot_sheet as workSheet
dim pivot_cache as pivotCache
dim pivot_table as pivotTable
dim pivot_table_upper_left as range
dim pf_col_1 as pivotField
dim pf_col_2 as pivotField
call importCSV(cur_working_dir & "\pivot.csv", activeSheet, range("$a$1"), "csv_data")
'
set pivot_sheet = sheets.add
set pivot_cache = activeWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "csv_data", Version:=xlPivotTableVersion14)
set pivot_table_upper_left = pivot_sheet.range("C3")
set pivot_table = pivot_cache.CreatePivotTable ( TableDestination:= pivot_table_upper_left )
set pf_col_1 = pivot_table.pivotFields("col_1")
set pf_col_2 = pivot_table.pivotFields("col_2")
call pivot_table.addDataField(pf_col_2, "Count of col_2", xlCount)
pf_col_1.orientation = xlRowField
pf_col_1.position = 1
pf_col_2.orientation = xlColumnField
pf_col_2.position = 1
activeWorkbook.saved = true
End Sub
private sub importCSV(csv_file_name as string, sheet_ as workSheet, range_ as range, name_ as string) ' {
'
' -> https://github.com/ReneNyffenegger/runVBAFilesInOffice/blob/master/Excel/ObjectModel/QueryTable/load_CSV.bas
'
With ActiveSheet.QueryTables.Add( _
Connection:= "TEXT;" & csv_file_name , _
Destination:=range_)
.name = name_
.fieldNames = True
.rowNumbers = False
.preserveFormatting = True
.textFilePlatform = 437
.textFileStartRow = 1
.textFileParseType = xlDelimited
.textFileTextQualifier = xlTextQualifierDoubleQuote
.textFileConsecutiveDelimiter = False
.textFileCommaDelimiter = True
.textFileTrailingMinusNumbers = True
.refresh BackgroundQuery:=False
end with
end sub
'
' ..\..\..\runVBAFilesInOffice.vbs -excel addLine -c Go
'
public sub Go()
dim line as shape
set line = create_line("b2", "e2")
set line = create_line("c3", "f9")
activeWorkbook.saved = true
end sub
private function create_line(fromCell as string, toCell as string) as shape
set line = activeSheet.shapes.addline( _
beginX := range(fromCell).left + range(fromCell).width / 2, _
beginY := range(fromCell).top + range(fromCell).height / 2, _
endX := range(toCell ).left + range(toCell ).width / 2, _
endY := range(toCell ).top + range(toCell ).height / 2 )
end function
'
' ..\..\..\runVBAFilesInOffice.vbs -excel page -c main
'
public sub main()
dim ps as pageSetup
set ps = activeSheet.pageSetup
ps.paperSize = xlPaperA4
ps.orientation = xlLandscape
activeWorkbook.saved = true
end sub
'
' ..\runVBAFilesInOffice.vbs -excel SetValueInRowAndColumn -c Run
'
public sub Run() ' {
dim row as long
dim col as long
for row = 1 to 10
for col = 1 to row
cells(row, col) = row * col
next col
next row
end sub ' }