Format Excel columns

[BSando]

I am exporting data to a new excel workbook but the dates are just numeric. how can I change the formatting of column A to a date when I export the table? Also the same with currency. I get the decimal places but I also want the dollar sign

----------------------------------

Dim sRecon As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

sRecon = "C:\3651H\3651H.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sRecon & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("monthly_sales", , adCmdTable)

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

xlApp.Application.Visible = True

Set xlSheet=xlApp.Workbooks.Open("C:\12.xls").Sheets(1)

xlApp.Worksheets.Add

xlSheet.Name = ([Forms]![monthly_report]![Month])

With xlSheet

.Range("C2").Value = "ABC-035 Total"
.Range("C3").Value = "AHJ-025 Total"
.Range("C4").Value = "AYB-1564 Total"
.Range("C5").Value = "GBC-011 Total"
.Range("C6").Value = "GHJ-047 Total"
.Range("C7").Value = "GYB-1256 Total"
.Range("C8").Value = "XBC-034 Total"
.Range("C9").Value = "XHJ-099 Total"
.Range("C10").Value = "XYB-1234 Total"
.Range("D2").Value = "=sum(Vlookup(""ABC-035"", C1549,2,FALSE))"
.Range("D3").Value = "=SUM(VLOOKUP(""AHJ-025"",C1549,2,FALSE))"
.Range("D4").Value = "=SUM(VLOOKUP(""AYB-1564"",C1549,2,FALSE))"
.Range("D5").Value = "=SUM(VLOOKUP(""GBC-011"",C1549,2,FALSE))"
.Range("D6").Value = "=SUM(VLOOKUP(""GHJ-047"",C1549,2,FALSE))"
.Range("D7").Value = "=SUM(VLOOKUP(""GYB-1256"",C1549,2,FALSE))"
.Range("D8").Value = "=SUM(VLOOKUP(""XBC-034"",C1549,2,FALSE))"
.Range("D9").Value = "=SUM(VLOOKUP(""XHJ-099"",C1549,2,FALSE))"
.Range("D10").Value = "=SUM(VLOOKUP(""XYB-1234"",C1549,2,FALSE))"
.Range("C210").Font.Bold = True
.Range("A11").CopyFromRecordset rs
.Range("A1").Value = "Date"
.Range("B1").Value = "Store"
.Range("C1").Value = "Product Code"
.Range("D1").Value = "Sales (inc GST)"
.Range("A11").Font.Bold = True

End With

xlApp.Quit

Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[SkipVought]

hi,

Assuming that column C is date and column D is currency

.range("C:C").numberformat = "mm/dd/yyyy"
.range("D:D").numberformat = = "$#,##0.00"

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[BSando]

Skip,

You are the business

Some people make things happen, some watch while things happen, and some wonder 'What happened?'