AutoFit Contents in cells

[Tamer]

Hi everybody,
I have several Access queries that I successfully exported
to several worksheets in an Excel file through an event
click procedure. My problem is that I want to autofit the
cells contents in excel as well as put borders around the
cells as part of my event click code. I used
xlsheet.Cells.AutoFit and xlsheet.Cells.BorderAround, but
they returned an error message. Any idea?? Thanks

 

[Joe Fallon]

You need to automate Excel and then call its functions.

Articles on How to Automate Excel:

I would like the data to simply be placed on a worksheet
that I've already spent time formatting (i.e. column widths, bolded column
headings...etc.)
Is there a way to do this?

You can't do this with TransferSpreadsheet.
You'll need to write VBA code that uses Automation to control Excel and
ensure
that the data ends up where you want it.

If you're new to Automation, here's a useful article giving the
basics of controlling Excel from Access:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/default.aspx?scid=KB;en-us;123859

ACC2000: Using Automation to Create and Manipulate an Excel
Workbook (Q210148)
http://support.microsoft.com/default.aspx?scid=kb;en-us;210148

ACC: Using Automation to Create and Manipulate an Excel
Workbook (Q142476)
http://support.microsoft.com/default.aspx?scid=kb;en-us;142476

Recent versions of Excel have a Range.CopyFromRecordSet method
that can be very handy for importing data from Access.

 

[Tamer]

Joe,
Thanks for you reply, but the articles you suggested me
seem not helping me much. My main issue is not how to send
the query results to the excel file and adding some values
or headers there - I can do this by using ADO or ActiceX
EXE methods. The only thing I need help with is: What is
the code to use from within Access to adjust the columns
width and add borders to the query results in the Excel
file.

Thanks.

 

[Joe Fallon]

You still need to automate Excel.
You can't do it "from within Access" directly.

Here is some sample code that does some things like widen columns.

It is not complete by any means.


'add reference to Microsoft Excel



Dim objXL As Excel.Application



Dim objWBS As Excel.Workbooks



Dim objWB As Excel.Workbook



Dim objWS As Excel.Worksheet



objXL = CType(GetObject(, "Excel.Application"), Excel.Application)



objWBS = objXL.Workbooks



objWB = objWBS.Add



objWS = CType(objWB.Worksheets(1), Excel.Worksheet)



'Bold and widen all the column headings



objWS.Range("A1:X1").Font.Bold = True



objWS.Columns.ColumnWidth = 10.5



'make some columns wider than the others



objWS.Range("A:A").ColumnWidth = 17



objWS.Range("C:C").ColumnWidth = 39



objWS.Range("E:F").ColumnWidth = 20



objWS.Range("L:O").ColumnWidth = 20



'rename the sheet to the file name w/o extension



objWS.Name = Mid(Path.GetFileName(strPath), 1,

Len(Path.GetFileName(strPath)) - 4)