Discussion: Fully customized export data from Access to Excel

[Mihail]

Hello !
I read a lot about transfer data from Access to Excel.
However I have not yet, in my brain, a clear stack. So, I ask you to help/assist me.
Thank you !

All work must be done in Access environment.
So:

Step 1. Create an Excel workbook.
How to create an Excel workbook at a certain location (folder) and with a certain name: "ExcelWorkBook" ?

[NeoPa]

This could be handled one of two ways, but the issues surrounding it are various. This is really a multi-faceted question rather than a discussion Mihail, but let's give some basic pointers and you can take them further where required :

1. DoCmd.TransferSpreadsheet() is one approach.
2. Application Automation is another.

#2 can be very helpful if you want more than a simple data dump. #1 can handle specification of the worksheet name, but only by naming the Access object that's transferred commensurately.

Determining exactly where a file should be stored can be done in so many ways, depending on your requirements, but Select a File or Folder using the FileDialog Object and Does a File Exist? may help there.

[Mihail]

Thank you for that, NeoPa.
After I am able to create an workbook from Access I'll return here for the next step.
If I can't do the task I'll return here anyway :).

[NeoPa]

Sounds good to me Mihail. I'm sure you'll manage it. One way or another :-)

[Mihail]

Step 1: Accomplished

Option Compare Database
Option Explicit
 
' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
' In VBE, goto Tools->References... and select it from the list
 
' Variable Declarations
Public objExcel As Excel.Application
 
Public Sub Test()
    Call CreateExcelObject
    Call AddWorkbook(True)
    Call SaveWorkbook("C:\CreateExcelWorkbook_Test.xlsx")
    Call QuiteExcelObject
End Sub
 
Public Function CreateExcelObject() As Boolean
    CreateExcelObject = False
On Error GoTo ErrorHandler
    ' If Excel is open, use GetObject, otherwise create a new Excel object
    Set objExcel = GetObject(, "Excel.Application")
    CreateExcelObject = True
 
Ex:
 
Exit Function
 
ErrorHandler:
    Select Case Err.Number
        Case 429 'Application not runing
            Set objExcel = New Excel.Application
            Resume Next
        Case Else
            MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
            CreateExcelObject = False
            Resume Ex
    End Select
End Function
 
Public Function QuiteExcelObject() As Boolean
On Error Resume Next
    objExcel.Quit
End Function
 
Public Function AddWorkbook(Optional MakeVisible As Boolean) As Boolean
    AddWorkbook = False
On Error GoTo ErrorHandler
 
    With objExcel
        ' Adds a new workbook to the Excel environment
        .Workbooks.Add
        ' Set the Excel window visibilitie
        .Visible = MakeVisible
    End With
    AddWorkbook = True
 
Ex:
 
Exit Function
 
ErrorHandler:
    Select Case Err.Number
        Case 429 'Application not runing
            MsgBox ("First you must create ExcelObject")
        Case Else
            MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
    End Select
 
    Resume Ex
End Function
 
Public Function SaveWorkbook(WkPath As String) As Boolean
    SaveWorkbook = False
On Error GoTo ErrorHandler
    With objExcel
        .ActiveWorkbook.SaveAs (WkPath)
    End With
    SaveWorkbook = True
 
Ex:
 
Exit Function
 
ErrorHandler:
    MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
    Resume Ex
End Function
 

[Mihail]

Now
Step 2. Working with Excel sheets.

2.1 How to count the sheets.
2.2 How to see the sheets names, how to change this name ? Here there are two points: The caption and the real name (the name used with in VBA editor).

I think I can do the job but, if someone point me in the right direction I do not waste time.
Thank you !

[MikeTheBike]

Hi

Just added some code to youe subroutine to illustrate what is possible etc.

Public Sub Test()
    Call CreateExcelObject
    Call AddWorkbook(True)
 
    With objExcel
        MsgBox .ActiveWorkbook.Sheets.Count
        MsgBox .ActiveSheet.Name
        .ActiveSheet.Name = "New Sheet Name"
        MsgBox .ActiveSheet.Name
 
        Dim sht As Worksheet
        For Each sht In .ActiveWorkbook.Sheets
            MsgBox sht.Name
        Next sht
    End With
 
    Call SaveWorkbook("C:\CreateExcelWorkbook_Test.xlsx")
    Call QuiteExcelObject
End Sub

There is also a Workbooks collection that you can also loop through in a similar way.

This may raise more questions, but hopefully not!


MTB

[Mihail]

:) Thanks Mike.
From now is a little bit of work to design some specific routines.
After that I'll return for next steps.
Thank you again !

[NeoPa]

For step #1 I would suggest that lines #40 to #43 might be redone as :

Public Function QuitExcelObject() As Boolean
On Error Resume Next
    Call objExcel.Quit
    Set objExcel = Nothing
End Function

The name had a strange spelling and objExcel was left as a refernce to a no-longer-valid object.

For step #2.1 I would simply point out that all collections have a .Count property.

For step #2.2 I would link you to a new article I just created for this situation - Excel Module (Code) Names.

Have fun :-)

[Mihail]

Thank you for your advices. Very useful.
I update the code as you can see.

Now I have almost all tools to make export-import.
The last step is (I think) to format cells.
Not as Font or Pattern (it will be great but not very useful).
So, how to format cells as Text, Numbers(Integer, Single, Double) and as Date ?

Option Compare Database
Option Explicit
 
Public Msg As VbMsgBoxResult
 
' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
' In VBE, goto Tools->References... and select it from the list
 
' Variable Declarations
Public objExcel As Excel.Application
 
Public Sub Test()
    Call CreateExcelObject
    Call AddWorkbook(True)
 
    With objExcel
        Msg = MsgBox("ActiveWorkbookName = " & .ActiveWorkbook.Name)
 
        'Count the sheets
        Msg = MsgBox("Sheets.Count = " & .ActiveWorkbook.Sheets.Count)
 
        'Rename Active Sheet
        .ActiveSheet.Name = "SheetNo1"
        Msg = MsgBox("ActiveSheet.Name = " & .ActiveSheet.Name)
 
        'Rename a sheet
        .Sheets(2).Name = "SheetNo2"
        Msg = MsgBox(".Sheets(2).Name = " & .Sheets(2).Name)
 
        'Activate a random sheet
        .Sheets("SheetNo2").Activate
        Msg = MsgBox("NewActiveSheet.Name = " & .ActiveSheet.Name)
 
        'Remove a sheet
        .ActiveSheet.Delete
        Msg = MsgBox("ActiveSheetAfterDeletion.Name = " & .ActiveSheet.Name)
 
        'Write to Excel in Active Sheet
        .ActiveSheet.Cells(2, 3) = "This will be write in Active Sheet"
        'Read from Excel from Active Sheet
        Msg = MsgBox("ReadFromExcel = " & .ActiveSheet.Cells(2, 3))
 
        'Write to Excel in a random Sheet
        .Sheets("SheetNo1").Cells(1, 1) = "This will be write in sheet named ""SheetNo1"""
        'Read from Excel from a random Sheet
        Msg = MsgBox("ReadFromExcel = " & .Sheets("SheetNo1").Cells(1, 1))
 
'        Dim sht As Worksheet
'        For Each sht In .ActiveWorkbook.Sheets
'            Msg = MsgBox("Sheet(" & sht.Index & ").Name = " & sht.Name)
'        Next sht
    End With
 
On Error Resume Next
    Kill ("C:\CreateExcelWorkbook_Test.xlsx")
        Call SaveWorkbook("C:\CreateExcelWorkbook_Test.xlsx")
        Msg = MsgBox("NewWorkbookName = " & objExcel.ActiveWorkbook.Name)
        Call QuiteExcelObject
End Sub
 
Public Function CreateExcelObject() As Boolean
    CreateExcelObject = False
On Error GoTo ErrorHandler
    ' If Excel is open, use GetObject, otherwise create a new Excel object
    Set objExcel = GetObject(, "Excel.Application")
    CreateExcelObject = True
 
Ex:
 
    Exit Function
 
ErrorHandler:
    Select Case Err.Number
    Case 429 'Application not runing
        Set objExcel = New Excel.Application
        Resume Next
    Case Else
        MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
        CreateExcelObject = False
        Resume Ex
    End Select
End Function
 
Public Function QuiteExcelObject() As Boolean
On Error Resume Next
    Call objExcel.Quit
    Set objExcel = Nothing
End Function
 
Public Function AddWorkbook(Optional MakeVisible As Boolean) As Boolean
    AddWorkbook = False
    On Error GoTo ErrorHandler
 
    With objExcel
        ' Adds a new workbook to the Excel environment
        .Workbooks.Add
        ' Set the Excel window visibilitie
        .Visible = MakeVisible
    End With
    AddWorkbook = True
 
Ex:
Exit Function
 
ErrorHandler:
    Select Case Err.Number
        Case 429 'Application not runing
            MsgBox ("First you must create ExcelObject")
        Case Else
            MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
    End Select
 
    Resume Ex
End Function
 
Public Function SaveWorkbook(WkPath As String) As Boolean
    SaveWorkbook = False
On Error GoTo ErrorHandler
    With objExcel
        .ActiveWorkbook.SaveAs (WkPath)
    End With
    SaveWorkbook = True
 
Ex:
Exit Function
 
ErrorHandler:
    MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
    Resume Ex
End Function

[NeoPa]

Mihail:
So, how to format cells as Text, Numbers(Integer, Single, Double) and as Date?

I suggest you set up a number of cells, each with a different format that you're interested in knowing about, then print the .Format property of each in the Immediate pane of the VBA IDE. That way you can see what format string is required for each.

[Mihail]

This is what i am looking for. The syntax for Format instruction.
I think that I can make an idea after I record a macro in Excel. But, if someone already know how to and wish to share...

[NeoPa]

You don't need to record a macro. That's not what I said. Just format a cell (EG A1.) in any way that you're interested in knowing about, then go to the VBA IDE, and the Immediate Pane within that, and type :

?Range("A1").Format

That takes less than half a minute. Alternatively I could print all the known possibilities which might take me 5 minutes. An easy choice I think.

[Mihail]

Oh, thank you !
Indeed, I don't understand what you say in post #11.
I'll try and I'll return for update the code.

Thank you again.

[NeoPa]

Mihail:
Indeed, I don't understand what you say in post #11.

That makes sense. I do try to make my English more basic for you Mihail, but sometimes I forget and use more advanced (and complex) language, which is harder for you to understand.

[Mihail]

I design a small program for importing data from Excel.
Of course you will find bugs and unhandled errors.
Please inform me about this and I'll try to solve it.
Thank you !