[AccessD] Access To Excel via a CSV File

Darryl Collins Darryl.Collins at coles.com.au
Thu Jun 5 19:06:42 CDT 2008


Max,

As an Excel Developer I would recommend Jenny's approach <below.. If you get stuck with anything Excel email me offlist at this email and I will see what i can do to help you out. I do a lot of stuff pushing and pulling data from Excel and Access, although I normally have Excel VBA doing the work, rather than Access VBA.

What you could do is have a pre-formatted excel template and just populate it directly.  I would only use the CSV route if you are having issues with the size of your dataset.  You can run the Excel code from Access which will allow you apply any formatting you like without having to do it manually.

If you have a large dataset (for example > 65535 rows) then using CSV out and a byte array into Excel is a better way to go.

cheers
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jennifer Gross
Sent: Friday, 6 June 2008 3:40 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access To Excel via a CSV File


Hi Max,

I do a lot of Access to Excel.  The only reason I would go to a CSV file
first is because there is a limit to the number of rows that you can send to
Excel - it is somewhere around 4,000.  If I know I am going to hit that
limit I go to CSV first, then open it in Excel and do the totals,
formatting, etc.  I leave Excel open and it is left to the user to do the
saving as xls.  I always keep Excel hidden until I am done so that users
don't start clicking away as data is transferring.

Also, I never use transferspreadsheet.  I always step through a recordset
and transfer data row by row.  It is quick and gives me much more control.
For instance, date values have to be sent to Excel already formatted.  If
you don't format them first they will send as integer and it seems that no
amount of formatting in Excel will bring them back around to dates.

Here is some sample code, watch the word wrap.  The routines for getting
Excel open comes from Dev Ashish's site:

Public Sub ToExcel()
On Error GoTo ErrorHandler
    
    Dim strErrMsg As String  'for Error Handling
    Dim X As Integer
    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rsACData As DAO.Recordset

    Set db = CurrentDb
    Set qdf = db.QueryDefs("somequery")
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rsACData = qdf.OpenRecordset(dbOpenDynaset)
    
    'export data to Excel
    If (Not rsACData.EOF) And (Not rsACData.BOF) Then
        If fIsAppRunning("Excel") Then
            Set objXL = GetObject(, "Excel.Application")
        Else
            Set objXL = CreateObject("Excel.Application")
        End If
        With objXL
            .Visible = False
            Set objWkb = .Workbooks.Add
            Set objSht = objWkb.Worksheets(1)
            With objSht
                .Cells(1, 1) = "Title"
                .Cells(2, 1) = "Another Title"
                .Cells(1, 1).Font.Bold = True
                .Cells(2, 1).Font.Bold = True
                
                .Cells(3, 1) = "Column Heading"
                .Cells(3, 2) = "Column Heading"
                .Cells(3, 3) = "Column Heading"
                .Cells(3, 4) = "Column Heading"
                .Cells(3, 5) = "Column Heading"
                .Cells(3, 6) = "Column Heading"
                .Cells(3, 7) = "Column Heading"
                
                .Rows(3).Font.Bold = True
                .Rows(3).WrapText = True
                
                .Columns(1).ColumnWidth = 10
                .Columns(2).ColumnWidth = 15
                .Columns(3).ColumnWidth = 10
                .Columns(4).ColumnWidth = 30
                .Columns(5).ColumnWidth = 10
                .Columns(6).ColumnWidth = 10
                .Columns(7).ColumnWidth = 10
                                
                X = 4
                rsACData.MoveFirst
                Do Until rsACData.EOF
                    .Cells(X, 1) = rsACData!SomeField
                    .Cells(X, 2) = rsACData! SomeField
                    .Cells(X, 3) = rsACData! SomeField
                    .Cells(X, 4) = Format(rsACData! SomeDateField, "Short
Date")
                    .Cells(X, 5) = rsACData! SomeField
                    .Cells(X, 6) = rsACData! SomeField
                    .Cells(X, 7) = rsACData! "=B" & X & "+C" & X & "+E" & X
& "+F" & X
                    X = X + 1
                    rsACData.MoveNext
                Loop

	      'totals
                    .Cells(X, 1) = "Totals"
                    .Cells(X, 2) = "=Sum(B4:B" & X - 1 & ")"
                    .Cells(X, 3) = "=Sum(C4:C" & X - 1 & ")"
                    .Cells(X, 5) = "=Sum(E4:E" & X - 1 & ")"
                    .Cells(X, 6) = "=Sum(F4:F" & X - 1 & ")"
                
                .Columns(5).NumberFormat = "#,##0.0_)"
	    .Columns(6).NumberFormat = "$#,##0.00_);[Red] ($#,##0.00)"
                
                .PageSetup.PrintGridlines = True
                .PageSetup.Orientation = xlPortrait
                .PageSetup.PrintTitleRows = .Rows(2).Address
                objXL.Visible = True
                objXL.Cells(4, 1).Select
                objXL.ActiveWindow.FreezePanes = True
                            
            End With
        End With
    Else
        MsgBox "No data", vbOKOnly + vbExclamation, "No data"
    End If
    

ExitHere:
    qdf.Close
    rsACData.Close
    Set db = Nothing
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
    
ErrorHandler:
    Select Case Err
        Case 91
            'do nothing - object never opened
            Err.Clear
            Resume Next
        Case 2501
            'no data in report - do nothing
            Err.Clear
            Resume Next
        Case Else
            strErrMsg = "An error occurred in " & "ToExcel" & vbCrLf &
vbCrLf & vbCrLf
            strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) &
vbCrLf & vbCrLf
            strErrMsg = strErrMsg & "Error Description: " & Err.Description
            MsgBox strErrMsg, vbInformation, "ToExcel"
            Resume ExitHere
    End Select
End Sub

I hope this helps,

Jennifer

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, June 05, 2008 6:34 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Access To Excel via a CSV File


Can any of you Access/Excel gurus help here please.
I need to create a csv spreadsheet by code.  When it is complete, I need to
put formatting on it. Ie,make columns into Currency Types. Bold some Rows,
set some bacground colours, etc.
I have got as far as creating the CSV ok but then I am stumped.
Any advice most welcome.
Ta
Max

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses.  No warranty is made that this material is free from computer virus or any other defect or error.  Any loss/damage incurred by using this material is not the sender's responsibility.  The sender's entire liability will be limited to resupplying the material.




More information about the AccessD mailing list