Understanding Excel formatting

[ungers]

Hi,

I am trying to export and then format a series of queries into worksheets in excel.

I have managed so far to get this to work and I have managed to achieve some formatting but I don't really understand why it is doing what it is doing and some things that I think should work, don't!!

Here is a selection of the code that I have working, I have managed so far to change the worksheet names, autofit the columns, and apply some font changes:

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWB = objXL.WorkBooks.Open(strPathFile)

objXL.UserControl = True

Set xlWB = objXL.WorkBooks.Open(strPathFile)


Dim intCountofSheets, intCurrentSheet As Integer
intCountofSheets = xlWB.Sheets.Count
intCurrentSheet = 1


xlWB.WorkSheets(1).Activate
objXL.ActiveSheet.Name = "Income"

xlWB.WorkSheets(2).Activate
objXL.ActiveSheet.Name = "Expenditure"

xlWB.WorkSheets(3).Activate
objXL.ActiveSheet.Name = "Recharges"

xlWB.WorkSheets(4).Activate
objXL.ActiveSheet.Name = "Interest & Tax"


Do While intCurrentSheet <= intCountofSheets
xlWB.WorkSheets(intCurrentSheet).Activate

objXL.ActiveSheet.Cells.Select
With objXL.Selection.Font
.Name = "Tahoma"
.Size = 10
End With


With objXL.ActiveSheet
.Columns.Select
.Columns.EntireColumn.Autofit
.Range("A1").Select 'Selects first cell to deselect the rest of the sheet.

End With
intCurrentSheet = intCurrentSheet + 1
Loop


xlWB.WorkSheets(1).Activate 'Returns the selection to the first worksheet.

xlWB.Save

Set xlWB = Nothing
Set objXL = Nothing

As I said I don't really understand, just lots of reading and trying to adjust example codes and other helpful sources. Could someone please explain a few basics for me?

In particular: Why does some of my code work with "with" and using "objXL." as a precursor and other parts work as just .column.select or .name for example?

With that in mind how do you specifically navigate to certain cells or columns?

Having got this far I would like to improve my programming so that it is a bit less trial and error and a bit neater!!

Thanks in advance!!

 

[Rx_]

I use to teach a 5 day Microsoft Certified Class - Excel Object Model Programming. It was an extremely busy class and required completing the Beginning Visual BASIC 6.0.

You are lucky. There are two books on Amazon Beginning Programming with Excel and Advanced Programming with Excel.

First thing - print off a copy of the Excel Object Model. There is a small one and the full one. Look at this chart three times every day. When I taught the course, I drilled this into everyone and quizzed them like a military boot camp Sargent. If you don't understand the object model and the hierarchy, you will never truly get it.

If you have programming experience, you should be able to get through the books above with hands on programming in a week (part time).

My favorite dog'eared book of all time is the Microsoft Excel 97 Developer's Handbook by Eric Wells Microsoft Press. Over 600 pages of pure genius code.

Given your question, that is the best I can do for you. I am finishing a 62 hour week just now and will be happy to answer specific questions in my spare time after you put a little effort into learning some basics on your own.