VBA Create / delete spreadsheet via function
Posted on 2013-12-24
I'm finishing up an app in Access 2010 and wish to generate results within a spreadsheet created on the fly. I've worked with doing this in VBA before, but mostly the code was specific for a certain spreadsheet. A sub for this sheet, a sub for that. And all subs created the sheet, filled in information and then closed them.
This time I would like to modularize it as much as possible. I'm trying to create a sub that I could call with a name and it would create a spreadsheet and allow me to edit it. The code in each main sheet would look like this:
MySheet = CreateSheet("C:\MySheets\NewSheet")
.ActiveCell.FormulaR1C1 = sClientName & " - A really important sheet"
I usually start with this in my create:
sub ThisRoutine(sSheetName as string)
'this is the beginning of my standard sheet creation in EVERY Sheet SUB
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object
Dim sAnswer As String
If FileExists(sSheetName & ".xls") Then
sAnswer = MsgBox("File already exists. Do you wish to overwrite?", vbYesNoCancel)
Select Case sAnswer
iTemp = 1
sSheetName = sSheetName & "_" & Trim(str(iTemp))
Do While FileExists(sFullFileName & ".xls")
iTemp = iTemp + 1
sSheetName = Mid(sSheetName, 1, Len(sSheetName) - Len(Trim(str(iTemp - 1)))) & "_" & Trim(str(iTemp))
If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Set objXL = CreateObject("Excel.Application")
boolXL = True
Set objActiveWkb = objXL.Application.ActiveWorkbook
What's the best way to make this a CreateSheet routine and make sure everything is opened and closed properly?
Can I create a CloseSheet function likewise?