How to format column in Excel from code in Access form?

[Amit]

MS Access 2000, Windows XP
======================
Hi,

I have a form with a button to export data from a query into an Excel file.

One of the columns in the query is time in "hh:mm AM/PM" format. When I
click on the button and export data, and then open the Excel file, the data
in the column with the time values does not show the time. It displays
"1/0/1900" for all the records (the entire column), though if I click on the
cell, the edit at the top of the worksheet shows "1/0/1900" followed by the
actual time.

Increasing the column width does not display the time in the worksheet.

However, if I select the entire column, and format it to "time", it displays
time in the correct format.

So, I set upon trying to format the specific column using code in the form
itself (I do not want to trouble users to format the column if it can be done
using code).

But, the code I tried gives me an error message - "Object required". I did
add the code for fIsAppRunning() in a module (thanks to MVPS website).

I got this code (written by Douglas Steele) using google, and modified it
somewhat to suit my needs.

==================================================
Private Sub cmdExporttoExcel_Click()
On Error GoTo Err_cmdExporttoExcel_Click

If (Len(Me.txtFileName & "") < 1) Then
MsgBox "Please specify the full path name of the file."
Cancel = True
Me.txtFileName.SetFocus
Exit Sub
End If

Dim strOutputFile As String

strOutputFile = Me.txtFileName


'DoCmd.OutputTo acOutputQuery, "qsel_ContactDetails", acFormatHTML,
strOutputFile, True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qsel_ContactDetails", strOutputFile, -1

'===code to format column in Excel file =================
Dim objXL As Object
Dim boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If
objXL.Application.Workbooks.Open (strOutputFile)
Set objActiveWkb = objXL.Application.ActiveWorkBook

With objActiveWkb
.Worksheets("querydataContactDetails_80505.xls").Columns("I:I").Select

' I also tried replacing the above line with
' .Worksheets(1).Columns("I:I").Select
' with the same result

With Selection
<<<<< this is where I get the error >>>>>>>>>

.NumberFormat = "h:mm AM/PM"
End With
End With

objActiveWkb.Close SaveChanges:=True
If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing
Set objXL = Nothing
'===end code to format Excel column=============


Exit_cmdExporttoExcel_Click:
Exit Sub

Err_cmdExporttoExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExporttoExcel_Click
End Sub
=====================================

I'm not much experienced with objects, so it is quite possible that I am
missing some line of code.

Will appreciate any help to make this work, or if you have a better
suggestion.

Thanks.

-amit

 

[MacDermott]

Try using this line instead:
.Worksheets(0).Columns("I:I").Select

Post back if this doesn't help, or if you're looking for more understanding
of what's going on here.

 

[Amit]

Try using this line instead:
.Worksheets(0).Columns("I:I").Select

Post back if this doesn't help, or if you're looking for more understanding
of what's going on here.

Hi MacDermott,
thanks for your response.

I changed the code to what you suggested, and now I get an error message
"Subscript out of range".
The column in Excel file still remains the same.

Is there anything else I can try? Would also appreciate an explanation.

Thanks.

-Amit

 

[Amit]

Try using this line instead:
.Worksheets(0).Columns("I:I").Select

Post back if this doesn't help, or if you're looking for more understanding
of what's going on here.

When I use ".Worksheets(0)...", I get the out of range error message much
before selection (where the Worksheet is being instantiated), leading me to
think that the worksheets start from 1 and not 0 (as is the case with
tab-pages etc.).

When I change it to ".Worksheets(1)", the error occurs at the same place as
the first time - inside the second "With.." statement.

-amit

 

[MacDermott]

The best way I've found to get the correct syntax for Excel is to record a
macro in Excel.
Then take a look at the code it produces. This isn't always exactly
what you'll use in Access, but it can solve a lot of syntax issues.