Format Excel from Access

[Chutney via AccessMonster.com]

I have a function in Access that I use to format my Excel spreadsheets but I
am having trouble with one line of code. The following works as a macro in
Excel:

Sub Macro1()
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
End Sub

However, when I use the "Range(Selection, Selection.End(xlToRight)).Select"
in my Access code:

Dim appXL As Object
Set appXL = CreateObject("Excel.Application")
With appXL.Application
.Visible = False
.Workbooks.Open ("MyExcelFile")
.Range("A1").Select
.Range(Selection, Selection.End(xlToRight)).Select
.Selection.Font.Bold = True
End With

I get the error message: Error #91: Object variable or With block variable
not set

If I change ".Range(Selection, Selection.End(xlToRight)).Select" to ".Range
("A1:M1").Select" then the function works. However, I need to select the
column titles in row 1 dynamically because I do not know how many columns
there will be. Can anyone suggest what I need to make this work?

 

[David H]

Try posting in an Excel newsgroup. Although you're controlling Excel from
within Access, its still Excel that you're working with.

http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

 

[David H]

Or try an newsgroup dedicated to Office integration.

http://www.microsoft.com/office/com...dg=microsoft.public.office.misc&lang=en&cr=US

 

[ryguy7272]

Ok, check it out:

Set StartRange and End Range:

Sub StartEnd()
Dim StartCell, EndCell As Range
Set StartCell = Range("A3")
Set EndCell = Cells(Rows.Count, "A").End(xlUp)
Range(StartCell, EndCell).Select
End Sub


Sub zeroo()
n = Cells(Rows.Count, "A").End(xlUp).Row
Range("A3:A" & n).Select
End Sub

Similarly...
Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Finally...to control Excel from Access:
Option Explicit ' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub
Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub ControlExcel()
Dim strFile As String
strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Excel.xls"
‘Of course, this is just an example; put the actual path to your actual file
here...
' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here...

End With
' Close and Cleanup
xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing
End Sub

HTH,
Ryan---

 

[Chutney via AccessMonster.com]

Davd and Ryan,

Thanks very much for your replies. They were both most helpful. Ryan's made
me realize that I was trying to apply the format to the application rather
than the worksheet and David's led me to a posting by Jeannette Cunningham
that had a very detailed Access code for formatting Excel:
http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Ok, check it out:

Set StartRange and End Range:

Sub StartEnd()
Dim StartCell, EndCell As Range
Set StartCell = Range("A3")
Set EndCell = Cells(Rows.Count, "A").End(xlUp)
Range(StartCell, EndCell).Select
End Sub

Sub zeroo()
n = Cells(Rows.Count, "A").End(xlUp).Row
Range("A3:A" & n).Select
End Sub

Similarly...
Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Finally...to control Excel from Access:
Option Explicit ' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub
Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub ControlExcel()
Dim strFile As String
strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Excel.xls"
‘Of course, this is just an example; put the actual path to your actual file
here...
' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here...

End With
' Close and Cleanup
xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing
End Sub

HTH,
Ryan---

Or try an newsgroup dedicated to Office integration.

[quoted text clipped - 35 lines]