Manipulating Excel from Access with VBA

[LarryP]

Need some help on code to do a simple manipulation (autofit the columns) in a
new Excel file I just created from Access. I had code already to set the
view to landscape, but I've gotten lost trying to add a couple additional
lines to do the autofit. Rows preceded by ### below are pre-existing code
that worked fine, other lines are my failed attempt to add the new thing. It
fails on the line preceded by @@@, saying the method is not supported. I
know it's very important when doing this to create and set the appropriate
objects, but I've tried it several ways and figure it's time to ask the gurus.

###Set oXL = CreateObject("Excel.Application")
###' Open newly generated Excel file
###Set oBook = oXL.Workbooks.Open(strOutputFile)
###Set oSheet = oBook.Sheets("qryDDMaster")
###oSheet.Activate
###oSheet.PageSetup.Orientation = xlLandscape
oSheet.Cells.Select
@@@oSheet.Selection.Columns.AutoFit
oSheet.Range("A1").Select
###'Save the Excel file
###oBook.Save
###' Close the Excel file
###' Then release any outstanding object references.
###oBook.Close (True)
###oXL.Quit
###Set oSheet = Nothing
###Set oBook = Nothing
###Set oXL = Nothing

 

[Douglas J. Steele]

Odd. While I didn't actually test it through automation, I did test it
directly in Excel, and it worked.

Take a look at my July, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". I'm pretty sure the sample code does an
autofit.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[LarryP]

Thanks, Doug, I'll read through all that, give it a try, and re-post with
results. Just for curiosity, I note in your code

Range(.Columns(1), .Columns(1).End(-4161)).

What's the (-4161) about?

 

[LarryP]

Thanks, will read/try/re-post results. Just for curiosity, I see in your code

Range(.Columns(1), .Columns(1).End(-4161)).

What's the -4161 about?

(((This might be a double post, I clicked something unintentionally and got
back to a blank reply screen)))

 

[LarryP]

Okay, never mind that guy in the White House, YOU are the Messiah.

This

Range(.Columns(1), .Columns(1).End(-4161)).Columns.Autofit

worked perfectly, and it would have taken me a LONG time to figure it out.
Many, many thanks.

 

[Ken Snell [MVP]]

Note.... this code step:

Range(.Columns(1), .Columns(1).End(-4161)).Columns.Autofit

is going to result in an extra session (or more) of EXCEL running in your
Task Manager after your code ends. That is because you have unqualified
references in the code step. Note that .Columns has no qualifier. You need
to add the Worksheet object in the code:

Range(NameOfWorksheetObject.Columns(1),
NameOfWorksheetObject.Columns(1).End(-4161)).Columns.Autofit

 

[Douglas J. Steele]

I believe I explain in the article that the code is intended to be used in
conjunction with Late Binding, which means no reference needs to be set to
Excel. If you were to record a macro in Excel, you'd find it would use
xlToRight (to indicate that you want Column 1 and everything to the right of
it that has values. With Late Binding, you don't have access to intrinsic
constants such as xlToRight, so you either need to define a constant

Const xlToRight As Long = -4161

or use the value instead.

 

[Douglas J. Steele]

I suspect that the code has a

With NameOfWorksheetObject

and

End With

wrapped around that (and that it's actually .Range, not just Range)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Ken Snell [MVP]]

If the code is using the With statement, then the problem I note will not
occur. I didn't see the . in front of Range in the posted code step.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[LarryP]

Yes, it was in fact nicely "packaged" in a With - End With wrapper. I just
got careless and lopped off the leading period when cut-and-pasting my last
reply. Thanks to all, but still curious what the -4161 constant represents,
and where I would have found that piece of information if I didn't have you
guys helping me.

 

[Ken Snell [MVP]]

-4161 is the value of the xlToRight EXCEL intrinsic constant. The easiest
way to get the value of an EXCEL instrinsic constant is to open Visual Basic
Editor in EXCEL, make the Immediate Window visible, and type a question mark
followed by the intrinsic constant in the window, then hit Enter:

?xlToRight
-4161

When you use late binding, ACCESS has no knowledge of the EXCEL intrinsic
constants, so you must replace them with the actual numeric values in the
ACCESS code. If you use early binding (meaning that you set a reference to
EXCEL in VBE's References), you then can use the intrinsic constants in your
code -- however, early binding means that your code will work ONLY for the
version of EXCEL that is in the References. Late binding will work for any
EXCEL version.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/