asked on

VBA Create / delete spreadsheet via function

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")

With mySheet
.Range("A7").Select
.ActiveCell.FormulaR1C1 = sClientName & " - A really important sheet"
.......etc
end with

CloseSheet(Mysheet)

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
Case vbNo
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))
Loop
Case vbCancel
GoTo EXIT_ThisRoutine
End Select
End If

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.Workbooks.Add

Set objActiveWkb = objXL.Application.ActiveWorkbook
..........etc
===============================================

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?

J

Hamed Nasr

Upload what you have now, with test data.
What you want is: (modify if different)
Pass a name (x.xls) to sub to create xls document, issue necessary editing, then close file.x,xls.

ASKER CERTIFIED SOLUTION

Zack Barresse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jerry N

ASKER

Thanks so much - - prefect!

Zack Barresse

You're very welcome. :)

Zack

Jerry N

ASKER

Is there a way to set the directory where the workbook is created? I'll have the workbook automatically created for the user. I need to check their directory and if its not there, create it using the above Create. But it doesnt seem to accept the destination.

Zack Barresse

You can use the Dir() function to see if a file or folder exists. If you wanted it in a function you could use something like below. It's just one way to do it. Basically it's a function that returns a number based on what you want done. Pass the folder and if you want to force it created or not. Check the return to see what happened...

-1: Insufficient rights to create folder
0: User had choice to create and did not
1: Folder already exists
2: Folder created

Option Explicit

Sub Test()
Debug.Print GetFolder("C:\Users\Zack\Desktop\Test", False)
End Sub

Function GetFolder(ByVal FolderPath As String, Optional ByVal ForceCreate As Boolean = False) As Long
    Dim UserCreate              As Boolean
    Dim msgCreate               As VbMsgBoxResult
    '/// Check if folder exists
    If Dir(FolderPath, vbDirectory) = vbNullString Then
        '/// Force folder creation
        '/// Folder doesn't exist
        '/// Ask user if they want it created
        If ForceCreate = False Then
            msgCreate = MsgBox("Folder doesn't exist. Create it now?", vbYesNo, "Create folder?")
        End If
        UserCreate = ForceCreate = False And msgCreate = vbYes
        If UserCreate = True Or ForceCreate = True Then
            On Error Resume Next
            MkDir FolderPath
            On Error GoTo 0
            '/// Check if folder was created
            If Dir(FolderPath, vbDirectory) = vbNullString Then
                '/// Folder wasn't created
                'MsgBox "Folder not created. Check permissions.", vbExclamation, "ERROR!"
                'Exit Function
                GetFolder = -1
            Else
                'MsgBox "Folder created successfully!", vbInformation, "Success!"
                'Exit Function
                GetFolder = 2
            End If
        Else
            '/// Folder doesn't exist, user doesn't want to create it
            GetFolder = 0
        End If
    Else
        '/// Folder exists
        GetFolder = 1
    End If
End Function

Open in new window

This is just one option to have it in a function.

HTH

Zack