Export from Access to Excel question

[njb]

I need to export data from my Access database to this Excel spreadsheet:

http://www.co.el-dorado.ca.us/vendor_interface.xls

I know how to do a straight export but that leaves a basic grid. Is it possible to export and keep the formatting? Is this possible? If it would be better to import from Excel, that would be an ok solution. Any help that can be provided is greatly appreciated. I think I just need to be pointed in the right direction.

 

[easyit]

Hi,

Yes, there are several methods. I use the following function which pulls data from Access:

Sub ADOImportFromAccessTable(ByVal DBFullName As String, TableName As String, TargetRange As Range, Optional blFieldNames As Boolean)
Dim intColIndex  As Integer
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = DBFullName
cnn.Properties("Jet OLEDB:System database") = mdwlocation
cnn.Open UserId:="Admin", Password:=""

Set TargetRange = TargetRange.Cells(1, 1)

Set rst = New ADODB.Recordset
With rst

    .Open TableName, cnn, adOpenDynamic, adLockOptimistic
    If Not .EOF Then
        .MoveFirst

        If blFieldNames Then
            For intColIndex = 0 To rst.Fields.Count - 1 ' the field names
                TargetRange.Offset(0, intColIndex).Value = rst.Fields(intColIndex).Name
            Next
            TargetRange.Offset(1, 0).CopyFromRecordset rst ' the recordset data
        Else: ' No FieldNames
            TargetRange.Offset(0, 0).CopyFromRecordset rst ' the recordset data
        End If
    End If
   .Close
End With
Set rst = Nothing
cnn.Close
Set cnn = Nothing

End Sub

This function is called as :

Call ADOImportFromAccessTable("Full path to the DataBase", "your queryname or SQL code", Worksheets("worksheetname").Range("name of range"), False)

The target range can be a named range (as shown), so you you can postion the data very accurately.

EasyIT

 

[njb]

Great! Thanks for your help. I knew there had to be a way to do it, I just couldn't figure out the how.

 

[PHV]

Another common way is to simply use a QueryTable created by MSQuery.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886