REPOST: Unable to format Excel column using code in a Form

[Amit]

Reposting this from Friday.
I did check the corresponding code for what I want to do by recording a
Macro in an Excel worksheet, and it is the same code as I'm using.

I've also tried getting rid of the "With ..." statement, and instead using
"Selection.NumberFormat...".

The error message probably points to some issue with an object not being
instantiated, or missing in the code. But, I'm not experienced in OO
programming to figure out the solution.

The file name is being input using a text control on the form, which I
assign to a string variable. Since the file is being created, and I can open
it, I don't think this is an issue. Just mentioning this so that all the
pieces of the puzzle are there

Using MS Excel 97 SR 2.

Thanks.

-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 (hh:mm Am/PM).

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.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

 

[John Nurick]

Hi Amit,

As far as I can remember you can't use the Selection object unless the
worksheet in question is visible. But you don't need to: you can simply
set the NumberFormat property of a range on the worksheet. Something
like this should work:

With objActiveWkb
.Worksheets("Sheet1").Columns(9).NumberFormat = "h:mm AM/PM"
End With

 

[Amit]

Hi Amit,

As far as I can remember you can't use the Selection object unless the
worksheet in question is visible. But you don't need to: you can simply
set the NumberFormat property of a range on the worksheet. Something
like this should work:

With objActiveWkb
.Worksheets("Sheet1").Columns(9).NumberFormat = "h:mm AM/PM"
End With

Once again, an MVP saves the day !!
Thanks, John. I followed your suggestion, and after changing the code, it
worked and formatted the column.

Cheers,

-Amit

 

[Nuno Guerra]

I would like to make the same but it returns always an error.

I just want to make the first row bold and the autofit all the columns from
the existing excel file.

Can you help me?