Formatting Excel from Access using VBA

[hpg]

I am exporting a query to an Excel sheet and I need to customize the foramtting of the myXL.xls file. I used the 'help menu' for a start and this is my code, which doesn't format anything! Never used VBA before so if Im referencing completely wrong, that's why....

Dim MyXL As Object

Set MyXL = GetObject("C:\My Documents\filename&quot

MyXL.Parent.Visible = True
MyXL.Windows(1).Visible = True

MyXL.Range("A1&quot.Select
MyXL.Selection.EntireRow.Insert
MyXL.Range("A4:F4&quot.Select
With MyXL.Selection.Interior
.Pattern = MyXL.xlSolid
.ColorIndex = 15
End With

etc....
I also *attempt* to enter Excel into the Running Object table if it's not running, which doesn't work either. Any help will be appreciated.

hpg

 

[CautionMP]

Here is a thought (this is how I have controlled Excel form Access in the past). Open Excel and record a Macro that does what you want it to do, then open the Macro (Alt+F8) and copy the code into Access and preceed the line of Excel code with "MyXL." (this tells Access to pass the code to Excel and not try running it.
Another thought, if you are using a regular query (not one run from code) format the query in the QBE window and then when you export the query select the "Save As Formated" check box on the right side of the "Save As / Export" dialog box, this will preserve most of the formating from the QBE window.
Hope this helps.

 

[VBAfun]

hpa, it looks like your code is not establishing an Excel object - try this?


Dim objExcel as Object
Set objExcel = CreateObject("Excel.Application&quot
objExcel.Workbooks.Open 'put your path & filename here)
objExcel.WindowState = 2 'sets it for maximized window
objExcel.Visible = True 'Shows your Excel chart
Set objExcel = Nothing 'removes it when you end Excel