Formatting Excell from VBA

[BamaColtsFan]

Okay, I'm sure this has been asked and answered may times but I just don't seem to know how to ask the question so my search doesn't tell me what I need to know.

What I am doing is sending a bunch of data to Excel from Access using VBA. Now, the actual data transfer works perfectly (even if my code is a little clunky...) and everything arrives in the Excel file as I would expect. What I am having trouble with is formatting the data once I get it into Excel. The biggest problem is that I don't know where to start so I don't have ANY formatting at all right now.

So, I guess what I'm looking for is a push in the right direction. Anything you fine folks can suggest is very welcome! The code to send the data out to Excell is below:

Public Function CopyToWorkbook()

Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim myDept As DAO.Recordset
Dim newDept As String
Dim myCMD As DAO.Recordset
Dim newCMD As String
Dim myDep As DAO.Recordset
Dim newDep As String
Dim strPath As String
Dim SQL As String
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
Set myDept = db.OpenRecordset("qryDepartmentCodes")
Set myCMD = db.OpenRecordset("qryCommandCodes")
Set myDep = db.OpenRecordset("qryDeputyCodes")

strPath = newPath!Out_Path & "CombinedTimecards_Crosstab.xlsx"

' LoadFileName

     DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary"
     DoCmd.TransferSpreadsheet acExport, 8, "qryDelinquentList", strPath, True, "Delinquent_List"
         
     
DoCmd.SetWarnings False
     
' Start Export of Command Code Tabs

Do Until myCMD.EOF

newCMD = myCMD!CMD_Code

DoCmd.OpenQuery "xDeleteCommandDelinquentList"
    
  SQL = "INSERT INTO tempCommandDelinquentList " & _
        "SELECT qryDelinquentList.[Dept], qryDelinquentList.[Title Rank], " & _
        "qryDelinquentList.[Name], qryDelinquentList.[SkillType], " & _
        "qryDelinquentList.[People Group], qryDelinquentList.[Time Approver], " & _
        "qryDelinquentList.[Person Types], qryDelinquentList.[Status], " & _
        "qryDelinquentList.[Reason], qryDelinquentList.[Timecard Start Date], " & _
        "qryDelinquentList.[Timecard Stop Date] " & _
        "FROM qryDelinquentList " & _
        "WHERE qryDelinquentList.[CMD_Code] ='" & newCMD & "';"

 DoCmd.RunSQL SQL
     
     DoCmd.TransferSpreadsheet acExport, 8, "tempCommandDelinquentList", strPath, True, newCMD
     
myCMD.MoveNext

Loop

DoCmd.OpenQuery "xDeleteCommandDelinquentList"
     
' Start Export of Deputy Code Tabs

Do Until myDep.EOF

newDep = myDep!Dep_CDR

DoCmd.OpenQuery "xDeleteDeputyDelinquentList"
    
  SQL = "INSERT INTO tempDeputyDelinquentList " & _
        "SELECT qryDelinquentList.[Dept], qryDelinquentList.[Title Rank], " & _
        "qryDelinquentList.[Name], qryDelinquentList.[SkillType], " & _
        "qryDelinquentList.[People Group], qryDelinquentList.[Time Approver], " & _
        "qryDelinquentList.[Person Types], qryDelinquentList.[Status], " & _
        "qryDelinquentList.[Reason], qryDelinquentList.[Timecard Start Date], " & _
        "qryDelinquentList.[Timecard Stop Date] " & _
        "FROM qryDelinquentList " & _
        "WHERE qryDelinquentList.[Dep_CDR] ='" & newDep & "';"

 DoCmd.RunSQL SQL
     
     DoCmd.TransferSpreadsheet acExport, 8, "tempDeputyDelinquentList", strPath, True, newDep
     
myDep.MoveNext

Loop

DoCmd.OpenQuery "xDeleteDeputyDelinquentList"
     
'  Start Export of Department Code Tabs
     
Do Until myDept.EOF

newDept = myDept!Dept

DoCmd.OpenQuery "xDeletetempDeptDelinquentList"
    
  SQL = "INSERT INTO tempDeptDelinquentList " & _
        "SELECT qryDelinquentList.[Dept], qryDelinquentList.[Title Rank], " & _
        "qryDelinquentList.[Name], qryDelinquentList.[SkillType], " & _
        "qryDelinquentList.[People Group], qryDelinquentList.[Time Approver], " & _
        "qryDelinquentList.[Person Types], qryDelinquentList.[Status], " & _
        "qryDelinquentList.[Reason], qryDelinquentList.[Timecard Start Date], " & _
        "qryDelinquentList.[Timecard Stop Date] " & _
        "FROM qryDelinquentList " & _
        "WHERE qryDelinquentList.[Dept] ='" & newDept & "';"

 DoCmd.RunSQL SQL
     
     DoCmd.TransferSpreadsheet acExport, 8, "tempDeptDelinquentList", strPath, True, newDept
     
myDept.MoveNext

Loop

DoCmd.OpenQuery "xDeletetempDeptDelinquentList"

DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acExport, 8, "CombinedTimecards_Crosstab", strPath, True, "CombinedTimecards_Crosstab"

End Function

 

[boblarson]

Instead of using the TransferSpreadsheet code, perhaps you might want something like I have on my website (and I have a little formatting there as an example):

From a form

Table/Query

Table/Query to a new, specifically named worksheet

I've found that you can get the basic code from recording a macro in Excel and then looking at it to see how you do it. You have to modify it slightly to work with Access and you should always use some object to refer to the code (for example in Excel it will say SELECTION ...etc. and you need to use the application object you've created in Access - objXL.Selection...etc. otherwise it will lock open a hidden instance of Excel on your machine until you close Access).

Hopefully that helps.