Simple Excel syntax needed

[ChopinFan]

I'm automating Excel from Access and need the syntax to change column widths. The built-in help file isn't helping. I have the following excel object variables:

Dim xlApp As Excel.Application
Dim xlWBook As Excel.Workbook
Dim xlWSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.application")
Set xlWBook = xlApp.Workbooks.Add
Set xlWSheet = xlWBook.Sheets("sheet1")


Thanks!

 

[bclt]

Sheet1.Columns("A:A").ColumnWidth = value
(in pixels)

 

[bclt]

Or as you did a reference to sheet1 (last line of your code):

xlWSheet.Columns("A:A").ColumnWidth = value

 

[ChopinFan]

Wonderful, thanks! I forgot one other issue. I also want E2:E1000 to be formatted to display dates like 5/17/05. Syntax?

 

[bclt]

I am not only Chopin fan but i play only chopin

xlWSheet.Range("E2:E1000").Select
Selection.NumberFormat = "m/d/yy;@"

My nick {bclt} means Beethoven Chopin Liszt Tschaikovsky!

Bye

 

[RoyVidar]

To avoid having extra instances of Excel in memory, also qualify the selection:

[tt]xlApp.Selection.NumberFormat = "m/d/yy;@"[/tt]

Or, just:

[tt]xlWSheet.Range("E2:E1000").NumberFormat = "m/d/yy;@"[/tt]

Roy-Vidar

 

[ChopinFan]

Such refined taste. I also play, and dream of being able to play Fantasy Impromptu one day, my all time favorite. Oddly, he himself didn't like it.

Hmm... I'm afraid this didn't work. The cells still display dates in General format. For example, 4/27/05 displays as 38469.

 

[RoyVidar]

I think we'd need to see in which context you are using it, cause it works like it should here.

Roy-Vidar

 

[ChopinFan]

Sorry, I responded to bclp hastily and didn't notice the next post. The more qualified selection works fine.

Thanks both!

 

[PHV]

And anyway feel free to play with the excel macrorecorder ...