HorizontalAlignment with Access VBA

[n0nick]

I create a Excel file and try to alignment text to the middle in the cells for the first row with this code:

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

ExcelSheet.Application.Cells(1, 1).Value = "Test"
ExcelSheet.Application.Rows(1).HorizontalAlignment = xlCenter

ExcelSheet.SaveAs "H:\TEST3.XLS"

ExcelSheet.Application.Quit

Set ExcelSheet = Nothing

But i got the error message: "Cannot set HorizontalAlignment-property to the Range-Class". Somebody know how I can fix this?

 

[xlbo]

Try having a look at the object model. The Horizontal Alignment property is NOT a property of a ROW but IS a property of a range.

Also - it looks like your references are a mess

Set ExcelSheet = CreateObject("Excel.Sheet")
means that "ExcelSheet" IS the application (ie Excel itself)

The application doesn't have a rows property - only a workSHEET has a rows property. You therefore

a: cannot use the syntax 'Excelsheet.Application' as they mean the same thing

b: cannot use 'ExcelSheet.Application.Rows' as the application does not have a 'rows' property

c: cannot use 'Rows(1).HorizontalAlignment' as the 'row' object does not have a horizontalalignment property - you must use 'EntireRow' or 'Range' to achieve this

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[n0nick]

Hello..Tnx for your answer! I found the answer by myself. I wrote:

ExcelSheet.Application.Rows(1).HorizontalAlignment = xlCenter

But when I'm in a Access VBA window Access dont cannot find variable "xlCenter", that is a Excel VB constant, so I just wrote value instedof variablename and it works.

I dont understand what you mean with that there are mess with referances. Everything is working great now..

 

[xlbo]

Read my post. You have set a reference to "Excelsheet". The reference you have set is the APPLICATION object. You then use

Excelsheet.Application

which is the same as using

Application.Application

comprende ???

You are also referencing the "Rows" property via the Application object (should be via the WORKSHEET object) and the "HorizontalAlignment" property via the "Row" object (should be the "Entirerow" or "Range" object) - neither of which would work in any language other than VBA - your code ONLY works because VBA is clever enough to insinuate what you are trying to do.

Technically your code is a complete mess and full of errors - I repeat again - it is ONLY working as VBA is doing some interpretation for you. If you want to write any more complex code, you would do better to write simple code properly 1st. Do yourself a favour and look at the object model of excel (F2 whilst in the VBE)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[n0nick]

Ok, now I understand you. If I write something like this:

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.OPEN("test.xls").Sheets(1)

xlSheet.cells(1, 1).Value = "Test"

Is this what you mean? How do you mean I should set horizontalalignment to center? Like

xlSheet.Range("A11").HorizontalAlignment = xlCenter

And if I want to set properties for fonts, background etc. I should use xlSheet.Cells.Fonts... etc?

 

[xlbo]

Spot on ! You will run into problems later on if you continue to reference objects as you were doing.

Best bet to set a range property for a row is to use the ENTIREROW property:

xlSheet.Rows(1).ENTIREROW.HorizontalAlignment = xlCenter

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[n0nick]

But the code i wrote in my last message:
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.OPEN("test.xls").Sheets(1)

requires a already created Excel file named "test.xls". But i want to create new Excel file in the code, how should i write then?

 

[xlbo]

Set xlSheet = xlApp.Workbooks.ADD

Again - the object model is your friend. All of this info is available by pressing F2 when in the VBE

You select an object and can see all of its methods and peroperties. There are also plenty of examples in the help files for this



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[n0nick]

Oh, i didn't know of the object model by pressing F2.. i was actually searching after a object model in the Help file.. Not i understand how this object works..

I have to write like this when i want to create new Excel file:

Set xlApp = CreateObject("Excel.Application")
Set xlWoorkbook = xlApp.Workbooks.Add
Set xlSheet = xlWoorkbook.Sheets(1)
xlSheet.cells(1, 1).Value = "Test"

xlWoorkbook.SaveAs "H:\test.xls"
xlWoorkbook.Close

Is this correct code?

 

[xlbo]

That is correct syntactically

However - for the code that you have written, I wouldn't bother with decalaring all the objects unless you are going to do more with them. The point about setting an object to a variable is to reduce the amount of typing necessary and to increase readability of code (as well as the possibility of speeding up execution). If all you are doing is what you have shown, you could write it as follows:

Set xlApp = CreateObject("Excel.Application")

xlApp.Workbooks.Add

with Activeworkbook
  .sheets(1).cells(1, 1).Value = "Test"
  .SaveAs "H:\test.xls"
  .Close
end with

If, however, you want to do more manipulation with the excel workbook and worksheet, then it is well worth declaring them as you have shown.

ie - for simple, easy code that is not very long, there is not a lot of point declaring the objects (Dim x as...). For longer, more complex code, it is certainly worthwhile declaring your objects and making sure that you have the right objects associated with the right variable etc.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[RoyVidar]

Oh - but - when referencing from Access - one must reference through relevant object, else one get's Excel automation fails second time code runs

[tt]xlApp.Workbooks.Add

with xlApp.Activeworkbook
.sheets(1).cells(1, 1).Value = "Test"
.SaveAs "H:\test.xls"
.Close
end with[/tt]

- though I prefer using object variables, see for instance INFO: Troubleshooting Error 429 When Automating Office Applications for some more details.

CreateObject and With block challenges, are described a little here XL2000: Automation Doesn't Release Excel Object from Memor

So I'd say n0nick's code is correct, though methods with less explicit referencing would also work, and often qualify in terms of correctness. What I miss, is releasing the objects, in the order they are created.

Roy-Vidar

 

[xlbo]

Roy - As I said - his code is correct but for the procedure shown it is (IMHO) overkill to declare a workbook and sheet object. Thanks for picking up the typo in my last post though - meant to include xlApp - the code was mainly to show that there was no need for XLWorkBook & XLSheet for the code that was shown

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[n0nick]

I see your points. I have experiance in programming in Java so I know all this about object referances. I am new in programming in VBA so I need little bit help with getting a picture of all objects.

RoyVidar: Im realesing object xlApp at the end:

Set xlApp = Nothing

This is enough?

 

[RoyVidar]

xlbo,

I wouldn't say correct, as the two latter links I provided, well, in my view anyway, indicates Microsoft acknowledges there are some challenges/flaws/bugs with usage of With blocks with CreateObject - and some hinting that adding a workbook without assigning it to an object variable might cause problems...

n0nick,

if you're using the last code you pasted, I'd expect releasing of all object variables - something like this?

[tt]set xlSheet=nothing
xlWoorkbook.SaveAs "H:\test.xls"
xlWoorkbook.Close
DoEvents
set xlWoorkbook=nothing
xlapp.quit ' you have this one?
DoEvents
set xlapp=nothing[/tt]

Roy-Vidar

 

[n0nick]

Ok, I have been listening to xlbo and keep just object xlapp, so i just need to realese it.

But why do i also have to write xlapp.quit?

 

[RoyVidar]

You don't have to, I was asking if you had it ;-)

It's closing (quitting) the excel application instance - hopefully not leaving an instance in memory - use it if you need it...

Roy-Vidar

 

[xlbo]

Roy - fair enough - I must admit that I rarely use undeclared code as most of the routines I use are complex and therefore do need (lots) of explicit references but when I have used code like that in the past for fairly minimal transfers, I have never run into any problems with it...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question