Font in Exported Spreadsheet

[millwheal]

I am using VBA [TransferSpreadSheet] to export a table from an Access 2003 database to Excel 2003.
I have no problems at all with the data, but the resulting spreadsheet has the 'MS Sans Serif' font, whereas the defualt font in Excel is 'Verdana'. I can find no settings in Access or Excel which specify 'MS Sans Serif'. Is there a way of ensuring the font is what I want it to be?
Thanks in anticipation.
millwheal

 

[JohnLee]

Hi,

In Access choose Tools, Options and then click on the datasheet tab. under default font you'll see a combo box which will list all the fonts available to you. choose your desired font. This will set the default font for your entire database to your chosen font.

John

 

[millwheal]

John,
Thanks for the suggestion, but that Option is already set to 'Verdana', yet the spreadsheet still ends up as 'MS Sans Serif'.
millwheal

 

[JohnLee]

Hi Millwheal,

Then in the spreadsheet, choose tools options, General tab, change the standard font to your desired font, that should resolve it.

John

 

[millwheal]

John,
I have already done that [as a 'default'], and I realise I could do it manually once the spreadsheet is created. However, I was hoping to do it automatically as I want to email the spreadsheet to someone else and was hoping to do the whole export/email as part of one, automated procedure.

millwheal

 

[boblarson]

You'll need to use the Excel Object model to do this. You will need to open the spreadsheet using code and set the font for the worksheet:

Dim objXL As Object
Dim xlWB As Object

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("ThePathAndFileNameToYourWorkBook")

With objXL
.Cells.Select

.Selection.Font.Name = "Verdana"

 

[millwheal]

Bob,
Many thanks, I can now do exactly what I wanted.

millwheal