Copying cells to Excel as I want them

[vfgilber]

Hi,

I would like to transfer records (all of them) from a table called COA_LOBJ to an Excel spreadsheet that is created by way of VBA. Here is the code I have so far (pilfered - I'm trying to adapt it). What you see below is the code to create the table and add titles in the first row. Now, I can't seem to figure out how to transfer records from Access to the spreadsheet. I need to be able to put specific columns in specific places so I don't want to just dump the whole table into the spreadsheet as is.

It seems that I don't know the basic language to reference parts of an Access table. I'm learning as I go, but I don't really understand how to use the Object Explorer.

Can anyone help?

Public Sub Command3_Click()

Dim objXL As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim objXLChart As ChartObject
Dim db As Database
Dim rs As Recordset
Dim strFile As String
Dim intLoop As Integer
Set objXLBook = objXL.Workbooks.Add
Set objXLSheet = objXLBook.Worksheets("Sheet1")
strFile = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(DIR(CurrentDb.Name))) & "Test.xls"
objXL.WindowState = xlMaximized
objXL.Visible = True
With objXLSheet
.Range("A1").Value = "Health Center Name"
.Range("A1").Cells.Font.Bold = True
.Range("B1").Value = "Last Name"
.Range("B1").Cells.Font.Bold = True
.Range("C1").Value = "First Name"
.Range("C1").Cells.Font.Bold = True
.Range("D1").Value = "Role"
.Range("D1").Cells.Font.Bold = True
.Range("E1").Value = "Address1"
.Range("E1").Cells.Font.Bold = True
.Range("F1").Value = "Address2"
.Range("F1").Cells.Font.Bold = True
.Range("G1").Value = "City"
.Range("G1").Cells.Font.Bold = True
.Range("H1").Value = "State"
.Range("H1").Cells.Font.Bold = True
.Range("I1").Value = "Zip Code"
.Range("I1").Cells.Font.Bold = True
.Range("J1").Value = "Phone"
.Range("J1").Cells.Font.Bold = True
.Range("K1").Value = "Email"
.Range("K1").Cells.Font.Bold = True
.Range("L1").Value = "FAX"
.Range("L1").Cells.Font.Bold = True
.Range("A1:L1").HorizontalAlignment = xlCenter
.Range("A2").Select
End With

Loop
With objXLSheet
.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("A1").Select
End Select

End Sub

 

[KeithG]

So basically you are using vba to export the data with custom column names? You can do this with a query.

 

[vfgilber]

I'm not sure how to do that either. I'm good at automating Excel, however, I have no idea what to do with Access.