Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Excel Range from Access
Good morning - I have an access query that I output as an excel file.
I would then like to manipulate and format the excel file to my liking, without manually opening the excel file and running a personal macro. As such, I've resorted to putting the macro that works fine in Excel into Access. I'm getting hung up on the following line of code, flagged at the bottom with asterisks: The error is "Run-time error 91 - Object variable or with block variable not set" Dim oxlApp As Object Dim oxlWb As Object Dim oxlWs As Object Set oxlApp = CreateObject("Excel.Application") oxlApp.Visible = True Set xlWb = oxlApp.workbooks.Open("f:\access\UZOrderReport.xls ") Set oxlWs = xlWb.Worksheets("qryOrderReporting:15-OrderCross") oxlWs.Name = "OrderReport" With oxlApp .ActiveWindow.Zoom = 85 .Range("o1").Select ***** .Range(.selection, .selection.End(xlToRight)).Select ****** .Selection.ColumnWidth = 6 End With I've tried the following alterations, to no avail: ..Range(.selection, .selection.End(xlToRight)).Select or ..Range(selection, selection.End(xlToRight)).Select or ..Range(.ActiveCell, .ActiveCell.End(xlToRight)).Select Any ideas? Thanks, Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Excel Range from Access
Dim rng as Object
oxlApp.ActiveWindow.Zoom = 85 set rng = oxlWs.Range(oxlWS.Range("O1"), _ oxlWS.Range("O1").End(-4161 )) rng.Entirecolumn.ColumnWidth = 6 -- Regards, Tom Ogilvy "Brian" wrote in message m... Good morning - I have an access query that I output as an excel file. I would then like to manipulate and format the excel file to my liking, without manually opening the excel file and running a personal macro. As such, I've resorted to putting the macro that works fine in Excel into Access. I'm getting hung up on the following line of code, flagged at the bottom with asterisks: The error is "Run-time error 91 - Object variable or with block variable not set" Dim oxlApp As Object Dim oxlWb As Object Dim oxlWs As Object Set oxlApp = CreateObject("Excel.Application") oxlApp.Visible = True Set xlWb = oxlApp.workbooks.Open("f:\access\UZOrderReport.xls ") Set oxlWs = xlWb.Worksheets("qryOrderReporting:15-OrderCross") oxlWs.Name = "OrderReport" With oxlApp .ActiveWindow.Zoom = 85 .Range("o1").Select ***** .Range(.selection, .selection.End(xlToRight)).Select ****** .Selection.ColumnWidth = 6 End With I've tried the following alterations, to no avail: .Range(.selection, .selection.End(xlToRight)).Select or .Range(selection, selection.End(xlToRight)).Select or .Range(.ActiveCell, .ActiveCell.End(xlToRight)).Select Any ideas? Thanks, Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Excel Range from Access
Tom - thanks for the quick reply. I'm an admitted VBA hack, so pardon
the stupidty of this question: From what I can tell, you've replaced the excel vba syntax of xlRight with -4161, correct? Are there other conventions for xlEnd, xlLeft, etc? Many thanks, Brian *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Excel Range from Access
since you are declaring your variables as Object, this would be an
indication that you are using late binding (not creating a reference to the Excel object library). In that situation, xlToRight would be an undefined variant variable (it is defined to have the value -4161 in the object library of Excel). All constants such as these have an assigned value - the function sees the assigned value, not the variable name xlToRight. When you reference the object library you have included implicit code such as Public Const xltoRight as Long = -4161 But if you use late binding then you have an implicit local declaration in any place where you use the constant of Variant xltoRight If you have infact created the reference, then you can change the -4161 to xltoright or leave it as is. You can see the values for constants in the object browser in Excel. -- Regards, Tom Ogilvy "Brian" wrote in message ... Tom - thanks for the quick reply. I'm an admitted VBA hack, so pardon the stupidty of this question: From what I can tell, you've replaced the excel vba syntax of xlRight with -4161, correct? Are there other conventions for xlEnd, xlLeft, etc? Many thanks, Brian *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select the first cell of the last row in a range - Excel 2003 | New Users to Excel | |||
Why does Excel select a range instead of just the cell clicked on | Excel Discussion (Misc queries) | |||
Select a range of non-adjacent cells in Excel? | Excel Worksheet Functions | |||
how can i randomly select names from adatabase in excel or access | Excel Discussion (Misc queries) | |||
How do I select on two variables in a range of data in excel | Excel Worksheet Functions |