Solved Export to Excel error '1004' With time format

[oxicottin]

I have a export to Excel and I keep getting an error message.

Runtime error '1004'
Numberformat method of Range failed

I and it takes me to a line in my code for a time field. The time in the excel workbook converts to 01/00/1900 for every record in the Excell workbook instead of the format from the query which the I convert time/date 04/18/2005 15:50:00 to 3:50pm using Test: TimeValue(TimeDelayBegan) in my query BUT in the workbook, it just converts the entire row to 01/00/1900 so I tried.

With XL
.Range("D1:H5000").NumberFormat = "hh:mm:ss"
End With

And that's what I get the '1004' error from. I also tried.

With XL
.Range("D1:H5000").SelectSelection.NumberFormat = "hh:mm:ss"
End With

And that didn't work either, I get an error '438' Doesn't support this property or method....

Thoughts?

 

[June7]

Works for me.

Perhaps you should provide entire procedure, maybe even db.

 

[oxicottin]

Works for me.

Perhaps you should provide entire procedure, maybe even db.

I cant its on a PC that I cant remove anything from so I would have to either build a database to show or type it all out.

 

[June7]

Okay, what is XL? Shouldn't it be a worksheet object?

 

[oxicottin]

I did notice something, I commented out the .Range("D1:H5000").NumberFormat = "hh:mm:ss" to extract the excel workbook without errors then looked at the time field and it shows the 01/00/1900 but when I click on the 01/00/1900 in the workbook it shows the time after the 01/00/1900 in the formula bar

Example: 01/00/1900 20:59:00

Why would it be adding or showing 01/00/1900?

@June7 below is almost the same code just different fields, I use this in several databases to extract to excel.

Sub ExportToExcelXLS()
'********************************************************************************
' Exports a file named [QC_Production_Export_MM-dd-yyyy.xls] to the desktop.
'********************************************************************************

    Dim outputFileName As String
    Dim XL As Object

    outputFileName = "C:\Documents and Settings\" & Environ("username") & "\Desktop\QC_Production_Export_" & Format(Date, "MM-dd-yyyy") & ".xls"

    If Len(Dir$(outputFileName)) > 0 Then
        Kill outputFileName
    End If

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_ExportToExcel", outputFileName, True

    Set XL = CreateObject("Excel.Application")
    XL.Workbooks.Open outputFileName
    XL.Visible = False

    With XL

        .Range("E2:E1000").NumberFormat = "hh:mm"     '"h:mm AM/PM"    'specified Time format was showing 1/0/1900
        .ErrorCheckingOptions.NumberAsText = False    'Clears the green error arrows
        .Range("A1:T1").Font.Bold = True
        .Range("A1:T1").Font.Name = "Segoe UI Light"
        .Range("A1:T1").Font.Size = 12
        .Range("A1:T1").Interior.ColorIndex = 44    'http://dmcritchie.mvps.org/excel/colors.htm
        .Range("A2:T1000").Font.Name = "Segoe UI Light"
        .Range("A2:T1000").Font.Size = 10
        .Columns("A:T").EntireColumn.AutoFit    'Auto fits colums to the largest text


    End With
    XL.ActiveWorkbook.Save
    XL.Application.Quit
    Set XL = Nothing

    MsgBox "Congrats your data has been uploaded to your desktop in a .xls file", vbInformation, "Upload Complete"

End Sub

 

[arnelgp]

record a macro and it will show you the correct way.
here is what i got:

With XL
.Range("D1:H5000").Select
Selection.NumberFormat = "h:mm:ss;@"
End With

 

[arnelgp]

i thin

Why would it be adding or showing 01/00/1900?

i think that is the First day since the Gregorian date was made?
if you did not supply the Date part (only you supply the hour/min/secs), it will come like that.

 

[oxicottin]

@arnelgp your post #6 worked... Thanks!

@arnelgp Can you explain what ;@ is for please?

 

[June7]

The @ character specifies Time format instead of Custom. Run code with and without @ then check the cell format in the Format Cells dialog.

 

[ebs17]

Set XL = CreateObject("Excel.Application")
With XL
   ' ...

With XL you create an Excel instance. An Excel instance has no ranges, cells, rows, columns; it is the pure Excel application.

So you have to reference the object model cleanly, and all the more precisely if you access the Excel model from outside.
You know the Excel object, comparable to a tree?

An Excel instance can contain open workbooks.
A workbook can contain several sheets/worksheets.
A worksheet has cells, rows, columns, ranges.

So if you want to target a specific range, you have to climb the tree. Jumping straight to the branch will often fail.

Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True   ' look what you have in this moment
Set oWB = oXL.Workbooks.Open(outputFileName)
Set oWS = oWB.Worksheets("Sheet1")

With oWS
    .Range("E2:E1000").NumberFormat = "hh:mm"
    ' ...