Formatting Export of Query To Excel

[Earl C Phillips]

I am trying to post a list of volunteers who will be at the Food Bank each day. When certain people sign on each morning, I execute within Form_Open DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryScheduledOfVolunteers", "\\core.harvesters.org\department\communityoutreach\VolunteerServices\volunteer-docs\GroupsHereToday-" & strDateOn & ".xls", , "OutreachList".

I have checked "qryScheduledOfVolunteers" formatting so that times appear as hh:mm before export, but they show up in the resultant spreadsheet as "1/0/1900." It also formats the spreadsheet in a narrow cell format that is nearly unreadable.

Is there a way to format the spreadsheet before-export to the maximum width of the recipient cell contents and export the time of day as HH:MM? I can hand-widen the cells and convert the time format to man-readable time of day, but I am trying to automate this process.

Any help would be appreciated. Help feed the hungry.

 

[Earl C Phillips]

I have solved all but the transfer of the formatted spreadsheet. I have formatted the spreadsheet headings for columns and cells as follows:
A SQL Stmt creates a recordset of expected volunteers:

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
xlApp.Visible = True
xlApp.UserControl = True
fldCount = rst.Fields.Count
'fills in the column headings
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
'determines version of Excel being used
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
xlWs.Cells(2, 1).CopyFromRecordset rst 'which is from the SQL
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit

'the unformatted hourly times are in column 5 and 6
For iCol = 5 To 6
For iRow = 2 To (recCount + 1)
xlWs.Cells(iRow, iCol).Value = Format(Cells(iRow, iCol), "Short Time")
Next
Next
End If

'this code is supposed to send the file to
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryScheduledVolunteers", "CORE\\department\communityoutreach\VolunteerServices\volunteer-docs\GroupsHereToday.xls", , , "OutreachList"

The last statement bombs. Is there a way to send a formatted Excel spreadsheet to an external file on a path from within Access VBA? Or do I have to send the spreadsheet to the path and try to format it there from within Access?

Any guidance would be appreciated. It helps to feed the hungry.

Earl C Phillips, Volunteer Programmer