help with formatting!

[jordanburch]

Hey guys I have the sub below in access. I am having a lot of trouble getting it to format the cells correctly. For whatever reason it takes the first three columns as general and the rest at dates, which they are cleraly not dates. I belive this export is the last function called that affects the file. I am wondering how can i force access to format each column as a certain format?

for example the file has the header as department and it needs to be a number how can i add that to the below code?

Any help is appreciated!



Private Sub cmdCF_Click()

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim lngCount As Long
Dim strFName As String, strSheet As String

strFName = txtOut.Value


FileCopy "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\UOT_Combine\M_Templatetest.xlsx", strFName
strSheet = "UOT"

Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(strFName)
Set xlWS = xlWB.Worksheets(strSheet)
Set rst = CurrentDb.OpenRecordset("qryOutput")

lngCount = rst.RecordCount

If lngCount > 0 Then
rst.MoveLast: rst.MoveFirst
lngCount = rst.RecordCount + 4
End If


xlWS.Range("A2:BU" & lngCount).CopyFromRecordset rst

rst.Close
Set rst = Nothing
xlWB.Close True
objXL.Quit
Set objXL = Nothing

MsgBox "Output File has been created.", vbOKOnly, "Process Complete:"

End Sub

 

[welshgasman]

And the data looks like what?