Contents 2 - In the beginning - Adding a Reference to Excel 3 - Opening Excel 4 - Creating/opening a Workbook 5 - Preparing to work with data (setting

and closing App/Book/Sheet references) 6 - Reading and writing data 7 - Basic formatting 8 - Finding "special" cells (bottom row etc) 9 - Installation Issues 10 - Using a Late-Bound connection to Excel 11 - "Excel doesn't close" 12 - How do I write code to ? [a.k.a. recording macro's] 13 - Useful functions and downloads 14 - Frequently Asked Questions

. Mar 8th, 2006, 04:32 AM si_the_geek Super Moderator #2 In the beginning Adding a Reference to Excel To start your journey into the world of automating Excel, you first need to tell your program that you want to reference Excel, to do this: Go to the "Project" menu, and select "References". You will be presented with a long list of

Join Date: Jul 02 Location: Bristol, UK Posts: 33,133

available references, just scroll down to "Microsoft Excel X.X Object Library" (where X.X is a version number - see post #14 for a list), then tick it and press OK.
NB: if it isn't in the list, there is probably an error with your installation of Excel. You can try to select the reference file manually by clicking the "browse" button, the file you need it is likely to be called something like "ExcelX.olb" (for Excel XP or later it seems to be "Excel.exe" instead).

As an aside, if you are releasing your software to various users then having a reference isn't the method you should use, but don't worry about it for now (this is the method you should use whilst developing your software, as it provides auto-complete features and Help while you

are writing your code).

. Mar 8th, 2006, 04:36 AM si_the_geek Super Moderator Opening Excel Once you have a reference set up, you can create an instance of Excel. To do this you declare an object variable with a data type of Excel.Application, and then you can do whatever you want with it, before closing/disconnecting from it. Join Date: Jul 02 Location: Bristol, UK Posts: 33,133 In this simple example we will start a new instance of an Excel application, display it, and then disconnect from it. You can copy this code and run it as it is: VB Code:
1. Dim oXLApp as Excel.Application 'Declare the object variable 2. 3. 4. 5. Set oXLApp = New Excel.Application 'Create a new instance of Excel 6. 7. 8. 9. oXLApp.Visible = True 'Show it to the user 10. 11. 12. 13. Set oXLApp = Nothing 'Disconnect from Excel (let the user take over) 14.

#3

NB: The final line is something that several people forget in this situation, but it is important - without it your program will take up memory which it isn't actually using, and after this part of your program has been run several times your computer may crash.

You can connect to an already open Excel application instead of opening a new one (using GetObject - as we will see later), but for

now it is better to just create a new instance, as it is more reliable and less confusing. You can also use CreateObject as a variation to "New ..", but we will also get to this later (post #10).

. Last edited by si_the_geek; Aug 19th, 2006 at 11:23 AM. Mar 8th, 2006, 04:40 AM si_the_geek Super Moderator Creating/opening a Workbook The example in the previous section was very basic, and to be honest pretty pointless too. In order to actually do anything you need to have a Workbook to interact with. Join Date: Jul 02 Location: Bristol, UK Posts: 33,133 There are times when you want to create a new Workbook, and times when you want to work with an existing one. To create a new Workbook: VB Code:
1. Dim oXLApp as Excel.Application 'Declare the object variables 2. 3. Dim oXLBook as Excel.Workbook 4. 5. 6. 7. Set oXLApp = New Excel.Application 'Create a new instance of Excel 8. 9. 10. 11. Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook 12. 13. 14. 15. oXLApp.Visible = True 'Show it to the user 16. 17. 18. 19. Set oXLBook = Nothing 'Disconnect from Excel (let the user take over)

#4

20. 21.

Set oXLApp = Nothing

To open an existing workbook, just change the " Set oXLBook =" line in the above example to this: VB Code:
1. Set oXLBook = oXLApp.Workbooks.Open("c:\my folder\my workbook.xls") 'Open an existing workbook 2.

By default a new Workbook has the number of sheets which are specified in the users options. You can set this option via code, but should change it back afterwards (else the user will have the option that you set). To do this you need a few extra lines around the "'Add a new workbook" line: VB Code:
1. 2. 3. Set oXLApp = New Excel.Application 'Create a new instance of Excel 4. 5. 6. 7. Dim iSheetsPerBook as Integer 'Add a new workbook (with one sheet) 8. 9. iSheetsPerBook = oXLApp.SheetsInNewWorkbook 10. 11. oXLApp.SheetsInNewWorkbook = 1 12. 13. Set oXLBook = oXLApp.Workbooks.Add 14. 15. oXLApp.SheetsInNewWorkbook = iSheetsPerBook 16. 17. 18. 19. oXLApp.Visible = True 'Show it to the user 20. 21.

Mar 8th, 2006, 04:44 AM

#5

si_the_geek Preparing to work with data (setting and closing App/Book/Sheet references) Super Moderator So far, we have got as far as having a Workbook open within the Excel application, which is getting better but doesn't let us do what we want - interact with the data! For those of you who have experience in this area, you will hopefully know already that we are missing a vital ingredient in our examples so far - the Worksheet. All data in a workbook is held in specific worksheets, so we should be using them. Now it is time to start with the proper examples. There are a few different ways of modifying/reading data, but all of them start with the same basic block of code, which we will call "Part A": VB Code:
1. Dim oXLApp as Excel.Application 'Declare the object variables 2. 3. Dim oXLBook as Excel.Workbook 4. 5. Dim oXLSheet as Excel.Worksheet 6. 7. 8. 9. Set oXLApp = New Excel.Application 'Create a new instance of Excel 10. 11. Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook 12. 13. Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet 14.

Join Date: Jul 02 Location: Bristol, UK Posts: 33,133

Note that you could open an existing workbook instead of creating a new one, by using the code in the previous post. This should be followed by some sort of modification/reading code, which we will come to in the next section - the important part for now is to finish properly, because if you don't then you won't see any of the changes you have made! When you have finished working with the data you can show, save, save as, or just close the workbook. There are different methods for each of these options, all of which we will call "Part B", which one of these you use is up to you (and will probably vary depending on the project you are writing at the time).

Part B(1) - Show the workbook: (best while you are writing or testing!) VB Code:
1. oXLApp.Visible = True 'Show it to the user 2. 3. Set oXLSheet = Nothing 'Disconnect from all Excel objects (let the user take over) 4. 5. Set oXLBook = Nothing 6. 7. Set oXLApp = Nothing

or, Part B(2) - Save the existing workbook you opened: VB Code:
1. Set oXLSheet = Nothing 'disconnect from the Worksheet 2. 3. oXLBook.Close SaveChanges:= True 'Save (and disconnect from) the Workbook 4. 5. Set oXLBook = Nothing 6. 7. oXLApp.Quit 'Close (and disconnect from) Excel 8. 9. Set oXLApp = Nothing

or, Part B(3) - Save the workbook to a new file: VB Code:

1. Set oXLSheet = Nothing 'disconnect from the Worksheet 2. 3. oXLBook.SaveAs "C:\My Documents\My File.xls" 'Save (and disconnect from) the Workbook 4. 5. oXLBook.Close SaveChanges:= False 6. 7. Set oXLBook = Nothing 8. 9. oXLApp.Quit 'Close (and disconnect from) Excel 10. 11. Set oXLApp = Nothing

or, Part B(4) - Close the workbook (but don't save): VB Code:
1. Set oXLSheet = Nothing Worksheet 'disconnect from the

2. 3. 4. 5. 6. 7. 8. 9.

oXLBook.Close SaveChanges:= False disconnect from) the Workbook Set oXLBook = Nothing oXLApp.Quit disconnect from) Excel Set oXLApp = Nothing

'Close (and

'Close (and

Once you have these two chunks of code you are ready to start the fun.

. Mar 8th, 2006, 04:48 AM

#6

si_the_geek Reading and writing data Super Moderator This code needs to be run on an open worksheet, so should be placed between "Part A" and "Part B" from the previous post. It is probably best to use B(1) for now, so that you can see what is going on more easily.

Join Date: Jul 02 Location: Bristol, UK Posts: 33,133

a) using Cells, Range (and UsedRange) There are a few methods of working with the data depending on what you want to achieve. The simplest method is to read/write a single cell, which can be done using the Cells collection of the WorkSheet like this: VB Code:
1. Dim my_variable As String 2. 3. my_variable = oXLSheet.Cells(2, 1).Value

This copies the text in the cell A2 (specified in the brackets: row 2, column 1) to the variable "my_variable". To write a single cell you just need to change the order of the expression like this: VB Code:
1. Dim my_variable As String 2.

3. 4. 5. 6. 7. 8. 9.

my_variable = "hello" oXLSheet.Cells(2, 1).Value = my_variable 'or just this: oXLSheet.Cells(2, 1).Value = "hello"

To read or write the Formula of the cell rather than the displayed text, just change the ".Value" to ".Formula", e.g.: VB Code:
1. oXLSheet.Cells(3, 1).Formula = "=A1+2"

All of the methods so far can also be used with the Range object instead of Cells, which allows you to specify the cell in the standard Excel "A1" address format, e.g.: VB Code:
1. oXLSheet.Range("B4").Value = "range test"

There is an advantage to using the Range object, in that you can work with multiple cells at the same time, for example you could set the value of all cells in the range D3:E5 to a single value like this: VB Code:
1. oXLSheet.Range("D3:E5").Value = 3

This may seem a little pointless, but with a little alteration you can see the power of this, as you can set all the values differently by using an array like this: VB Code:
1. Dim lMyArray(2, 1) As Long 2. 3. lMyArray(0, 0) = 1 4. 5. lMyArray(0, 1) = 2 6. 7. lMyArray(1, 0) = 3 8. 9. lMyArray(1, 1) = 4 10. 11. lMyArray(2, 0) = 5 12. 13. lMyArray(2, 1) = 6 14.

15.

oXLSheet.Range("D7:E9").Value = lMyArray

The location of the cells within the range can be deduced from the array positions in the same way as for the Cells collection, i.e.: (row, column). Unfortunately you can't quite do this the other way around (i.e.: you can't set lMyArray equal to the range), as VB will not allow you to assign to an array. Instead you need to use a Variant to hold the array instead, e.g.: VB Code:
1. Dim vArray As Variant 2. 3. vArray = oXLSheet.Range("D7:E9").Value

You can copy the entire used range of the spreadsheet simply using a special builtin range called UsedRange, like this: VB Code:
1. Dim vArray As Variant 2. 3. vArray = oXLSheet.UsedRange.Value 4. 5. 'vArray now contains the values of ALL the cells in used range of the worksheet. 6. 7. 8. 9. 10. 11. 'example usage of the array: 12. 13. Dim lngCol as Long, lngRow as Long 14. 15. For lngRow = 1 To UBound(vArray,1) 16. 17. For lngCol = 1 To UBound(vArray,2) 18. 19. MsgBox vArray(lngRow, lngCol) 20. 21. Next lngCol 22. 23. Next lngRow

Setting values in the spreadsheet using an array has the advantage of being far quicker, as each time you enter data Excel does some work like re-calculating formulas. Using an array means that this extra work is only done once for the entire array, rather than once per cell. b) using a RecordSet

There is one more way of putting data into Excel that is extremely useful, which is to copy data directly from a recordset that you have gotten from a database. You simply say which is the first cell to put the data into, and Excel works out the rest of the cells that are required (although you can specify the maximum rows/columns to use in two optional parameters). I have not created the recordset in these examples, as there are many ways in which you can do it. If you need help with this, please see the tutorials (or post a new thread) in the Database Development forum on this site. VB Code:
1. 'create and fill a recordset here, called oRecordset 2. 3. oXLSheet.Range("B15").CopyFromRecordset oRecordset

Note that you can also fill in the field names using an array like this: VB Code:
1. Dim iCount As Integer 2. 3. With oXLSheet 'Fill with data 4. 5. For iCount = 0 To (oRecordset.Fields.Count -1) 6. 7. .Cells(1, iCount+ 1) = oRecordset.Fields(iCount).Name 8. 9. Next iCount 10. 11. .Range("A2").CopyFromRecordset oRecordset 12. 13. End With

"But I thought setting individual Cells was slow?" It is, but to use Range you need to know the target cells, in order to do that you need to know the column address of "B" + oRecordset.Fields.Count, which requires a bit more work. Luckily I have provided a function in section 13 "Useful functions and downloads" called xl_Col, which returns the column name for the column number that you specify (there is also xl_ColNo, which converts the name back into a number). This function allows you to do use the Range object like this: VB Code:
1. Dim iCount As Integer 2. 3. Dim sFieldNames() As String 4. 5. ReDim sFieldNames(oRecordset.Fields.Count - 1) As String 6. 7. For iCount = 0 To (oRecordset.Fields.Count -1) 8.

9. 10. 11. 12. 13.

sFieldNames(iCount) = oRecordset.Fields(iCount).Name Next iCount oXLSheet.Range("A1:" & xl_Col (1 + oRecordset.Fields.Count) & "1")

. Last edited by si_the_geek; May 12th, 2006 at 08:04 AM. Reason: corrected "example usage of the array" (previously read rows as columns & vice-versa) Mar 8th, 2006, 04:52 AM si_the_geek Super Moderator Basic formatting You can format cells by code in a similar way to how you would manually. You can set things such as the font, the font style (bold, underline, etc), the alignment (left, right, etc) and the text colour. You can apply these formats to any kind of range, the two we have looked at so far (Cells and Range) and a few new ones.

#7

Join Date: Jul 02 Location: Bristol, UK Posts: 33,133

For example, to set the cell "A4" to be bold you can do the following: VB Code:
1. oXLSheet.Range("A4").Font.Bold = True turn bold off) 2. '(False to

Or to set it bold, italic, and underlined, you can do this: VB Code:

1. With oXLSheet.Range("A4") 2. 3. .Font.Bold = True '(False to turn bold off) 4. 5. .Font.Italic = True '(False to turn italic off) 6. 7. .Font.Underline = xlUnderlineStyleSingle 8. 9. End With

Note that Underline does not use Boolean values, as there are various

options for underlining (such as single or double underline). When you type in the = after Font.Underline a list of the values should appear, so that you can simply select the appropriate one (the value to turn underlining off is xlUnderlineStyleNone). To change the text colour of the same cell to green you can do this: VB Code:
1. With oXLSheet.Range("A4") 2. 3. .Font.ColorIndex = 50 4. 5. End With

To align the text within cells you can use a couple of extra properties, one for horizontal and one for vertical: VB Code:
1. With oXLSheet.Range("A4") 2. 3. .HorizontalAlignment = xlRight options include xlCenter and xlLeft) 4. 5. .VerticalAlignment = xlBottom options include xlCenter and xlBottom) 6. 7. End With

'(other '(other

The alternative range objects we are interested in are Rows and Columns. Both of these take a single parameter, which is the row or column number we want to work with, and can be used in the same way as Cells or Range, eg: VB Code:
1. oXLSheet.Rows(7).Font.Bold = True '(False to turn bold off) 2. 3. oXLSheet.Columns(3).Font.Bold = True '(False to turn bold off) 4.

You can also select multiple adjacent columns/rows by providing a string parameter instead of a number, which contains the first and last row/column separated by a colon (if you provide a string for Columns, it must be the column name rather than number), eg: VB Code:

1. oXLSheet.Rows("6:7").Font.Bold = True to turn bold off) 2. 3. oXLSheet.Columns("C:E").Font.Bold = True '(False to turn bold off) 4.

'(False

. Mar 8th, 2006, 05:01 AM si_the_geek Super Moderator Finding "special" cells (bottom row etc) As you may already know, when you are editing a spreadsheet manually you can press Ctrl and an arrow key to go to the next/last cell in that direction which contains text. There are also methods of performing this via code. Join Date: Jul 02 Location: Bristol, UK Posts: 33,133 Here is one method to find the last used row/column in the sheet: VB Code:
1. LastRow = oXLSheet.UsedRange.Rows.Count 2. 3. LastCol = oXLSheet.UsedRange.Columns.Count
NB: UsedRange is a special pre-defined range which contains the entire used area of the sheet.

#8

Here is an alternative - but note that this method assumes that every row has data in all columns (and vice versa). If this is not the case, it is safer to use the options above/below instead. VB Code:
1. LastRow = oXLSheet.Range("A1").End(xlDown).Row 2. 3. LastCol = oXLSheet.Range("A1").End(xlToRight).Column

There is also another built-in function in Excel for detecting used ranges, the .SpecialCells function retrieves a number of "special range" values depending on the constant value passed to the function. Here is the equivalent to the code above (works by going to the 'last' used cell): VB Code:

1. LastRow = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Row 2. 3. LastCol = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Col umn

Some of the other parameters for the SpecialCells method are:

xlCellTypeAllValidation - Cells having validation criteria xlCellTypeBlanks - Empty cells xlCellTypeComments - Cells containing notes xlCellTypeFormulas - Cells containing formulas

The full list can be seen in the Help or the Object Browser.

. Last edited by si_the_geek; Jun 24th, 2006 at 06:29 PM. Mar 8th, 2006, 05:06 AM si_the_geek Super Moderator Installation Issues As you are probably aware there are several versions of Excel, all of which have subtle variations in the way they work, and the features that they provide. Join Date: Jul 02 Location: Bristol, UK Posts: 33,133 In the vast majority of cases this will not affect the code you need to write to perform the tasks required on the document (unless you use 'new' features which aren't available in the version of Excel which is installed). There can however be a problem when your program tries to start Excel. The trouble is that with your installation package you installed a specific version of the Excel type library, which could easily be different from the version of Excel which is installed on the users computer. Even though the installed version provides essentially the same functionality as the one on your development computer, Windows may realise that there are version differences and stop your program from using something that it thinks is likely to cause errors. This has become even more pronounced recently, as versions of #9

Excel now get you to install an "Excel.exe" file along with your program, rather than the traditional "ExcelX.olb" file Another issue is that Excel may not even be installed, which can stop your program from even starting! The best way around these issues is to use a Late-Bound connection to Excel (see next section), with extra error handling around the first line (CreateObject/GetObject) to deal with the possibility that it isnt installed.

. Mar 8th, 2006, 05:09 AM si_the_geek Super Moderator Using a Late-Bound connection to Excel The code so far in this tutorial has been Early-Bound (i.e.: VB knows in advance what Excel functions are available to you), which as seen in the previous section can cause serious issues. Join Date: Jul 02 Location: Bristol, UK Posts: 33,133 Unless you can guarantee that all of your users have the same version of Excel installed as you do, I would strongly recommend converting to Late-bound for software that you release "into the wild". The down-side to using this is that you no longer get some of the nice features of the VB IDE (like the drop-down lists of properties and methods that appear when you type oXLApp. ), so it may be a good idea to convert to this method once your code is finished. There are four steps to convert your code: 1) Replace Excel data types with Object. If you have: VB Code:
1. 2. 3. 4. 5. 6. 7. Dim oXLApp as Excel.Application Dim oXLBook as Excel.Workbook Dim oXLSheet as Excel.Worksheet ...

#10

You need to change it to:

VB Code:
1. 2. 3. 4. 5. 6. 7. Dim oXLApp as Object Dim oXLBook as Object Dim oXLSheet as Object ...

Note that you should also do this with any other variables that you have declared as Excel.something

2) Change the initialisation of the application object. If you have: VB Code:

1. Set oXLApp = New Excel.Application

You should replace it with: VB Code:

1. Set oXLApp = CreateObject("Excel.Application")

Note: if Excel isnt installed, this line of code will cause an error you should deal with this by using error handling in an appropriate way for your project.

3) Define the constants that you have used. Excel constants (e.g.: xlLeft) are defined in the object library; however you will no longer have a link to this, so they wont be defined any more! There are two main options here; the first is to use the Object Browser to find the values/declarations for each one you have used (very slow, and prone to errors!), and the other (much simpler) is to add a pre-made module to your project which declares them all for you. You can find a link to one which Microsoft produced in the useful functions and downloads section (post #13).

4) Remove "Excel object Library" from the list in "Project" ->

"References"

Your code is now late-bound, and should work with all versions of Excel that support automation (as long as you havent used special functionality which wasnt available in earlier versions).

. Mar 8th, 2006, 05:18 AM si_the_geek Super Moderator "Excel doesn't close" If you find that after running your program you still have an open copy of Excel that you weren't expecting (either visible, or shown in Task Manager) then I'm afraid that you have made a mistake somewhere in your code. Join Date: Jul 02 Location: Bristol, UK Posts: 33,133 The usual cause is using objects that are ok in Excel VBA code, but not in a VB application. Common examples of this are:

#11

Application (which needs to be oXLApp) Cells (which should be oXLSheet.Cells) Range (which should to be oXLSheet.Range) WorkSheets (which should be oXLApp.WorkSheets) Selection* (which needs to be oXLApp.Selection) ActiveSheet* (which should be a variable like oXLSheet)

For any other Excel based items you use, you should do the same as all of the items in this list - specify the parent object (usually oXLSheet or oXLBook or oXLApp) that you want to refer to. * As a side note, it is advisable to avoid using Selection and ActiveSheet altogether (even in Excel VBA code), as this can be changed by the user if they are working in Excel while your code is running - which means you could well be working in the wrong worksheet/workbook!

It may be that you haven't used appropriate code to close the sheet/workbook/application (see post #5 for valid examples). This is particularly true if you have not used a variable to store the WorkSheet (as several people seem to do), as it is easy to refer to the WorkBook instead (which isn't technically valid, even tho it works); I would therefore strongly recommend using variable for it.

If as part of "closing down" you miss a child object variable (which is basically what happens in the situation described above) and destroy the application object, it will orphan the child object variable and cause the instance of Excel to remain because not all resources associated with the application instance have been closed and destroyed. . Last edited by si_the_geek; Feb 6th, 2009 at 01:07 PM. Reason: added more text Mar 8th, 2006, 05:26 AM si_the_geek Super Moderator How do I write code to ? [a.k.a. recording macro's] There are many things which can be done with Excel that haven't been listed here, and there is a good reason - Excel will tell you the code you need! Join Date: Jul 02 Location: Bristol, UK Posts: 33,133 If you record a macro in Excel, it is 'written' in VBA (a subset of VB), and this code can be copied almost directly into your VB program, with only a few modifications to use the objects you have created rather than the default Application/Sheet/etc objects [failure to do this part will most likely cause your program to have bugs, or even cause your computer to crash]. To record a macro:

#12

Go to "Tools" -> "Macro" -> "Record new macro". Manually do the things that you want your program to do. Press the "stop recording" button (a blue square). Go to "Tools" -> "Macro" -> "Macros...", which will show a list of macros. Select the macro you recorded, and click on "Edit" to see the code.

There are 4 steps you should perform when taking code from a macro to use in your program, they are: 1. Remove un-needed code (as Excel often does extra things, such as writing all values from an options screen). 2. Change ActiveDocument, Application etc to suit the

variables in your code. 3. Change Selection to oXLApp.Selection. 4. Prefix all instances of Range/Cells/Rows/Columns/etc with your sheet object.

Example 1: The following code is created if you select a range, then set it to Bold and Italic: VB Code:
1. Range("C9:F16").Select 2. 3. Selection.Font.Bold = True 4. 5. Selection.Font.Italic = True

In this case there is no un-needed code, so we can skip step 1. There is no use of ActiveDocument etc, so we can also skip step 2. Following step 3 gives us this: VB Code:
1. Range("C9:F16").Select 2. 3. oXLApp.Selection.Font.Bold = True 4. 5. oXLApp.Selection.Font.Italic = True

And step 4 gives us this: VB Code:

1. oXLSheet.Range("C9:F16").Select 2. 3. oXLApp.Selection.Font.Bold = True 4. 5. oXLApp.Selection.Font.Italic = True

As mentioned in the previous post, it is best to avoid Selection altogether. This can be done by changing the code to this: VB Code:
1. oXLSheet.Range("C9:F16").Font.Bold = True 2. 3. oXLSheet.Range("C9:F16").Font.Italic = True

..or you can do it like this instead (a bit more efficient, and easier to read): VB Code:
1. With oXLSheet.Range("C9:F16") 2. 3. .Font.Bold = True 4. 5. .Font.Italic = True 6. 7. End With

Example 2: As another example, here is part of the code created if you go into "File"->"Page Setup", then set the orientation to Landscape, and set the "rows to repeat at top" to $1:$2 VB Code:
1. With ActiveSheet.PageSetup 2. 3. .PrintTitleRows = "$1:$2" 4. 5. .PrintTitleColumns = "" 6. 7. End With 8. 9. ActiveSheet.PageSetup.PrintArea = "" 10. 11. With ActiveSheet.PageSetup 12. 13. .LeftHeader = "" 14. 15. .CenterHeader = "" 16. 17. ... lots of lines removed! ... 18. 19. .CenterVertically = False 20. 21. .Orientation = xlLandscape 22. 23. .Draft = False 24. 25. .PaperSize = xlPaperA4 26. 27. .FirstPageNumber = xlAutomatic 28. 29. .Order = xlDownThenOver 30. 31. .BlackAndWhite = False 32. 33. .Zoom = 100

34. 35.

End With

Following step 1 gives us this: VB Code:

1. With ActiveSheet.PageSetup 2. 3. .PrintTitleRows = "$1:$2" 4. 5. End With 6. 7. With ActiveSheet.PageSetup 8. 9. .Orientation = xlLandscape 10. 11. End With

Which can be shortened to this: VB Code:

1. With ActiveSheet.PageSetup 2. 3. .PrintTitleRows = "$1:$2" 4. 5. .Orientation = xlLandscape 6. 7. End With

From there, following step 2 gives us this: VB Code:

1. With oXLSheet.PageSetup 2. 3. .PrintTitleRows = "$1:$2" 4. 5. .Orientation = xlLandscape 6. 7. End With

In this case, there is nothing to do for steps 3 and 4.

. Last edited by si_the_geek; Mar 1st, 2007 at 02:55 PM. Mar 8th, 2006, 05:29 #13

AM si_the_geek Super Moderator Useful functions and downloads Excel constants module This is a pre-written module which contains all of the Excel constants (e.g.: xlLeft), to enable you to keep the constant names in your code when you are using Late-binding. Just add the module to your project! (NB: this download also contains similar modules for the other Office products). This is provided by Microsoft, and can be downloaded here: http://support.microsoft.com/kb/112671 (alternative) Note that the above file is designed for Excel 97, so it does not contain constants which have been added more recently (it does however contain almost everything you need). You can see a list of constants in the Excel 2003 constants page, which contains those for Excel 97 plus newer ones.

Join Date: Jul 02 Location: Bristol, UK Posts: 33,133

xl_col and xlColName Functions for converting column 'name' to/from column number. Simply add these functions to your code (in a module if you want to use them from multiple forms). VB Code:
1. Function xl_Col(ByRef Col_No) As String 2. 'returns Excel column name from numeric position (e.g.: col_no 27 returns "AA") 3. 'by Si_the_geek (VBForums.com) 4. 5. 'Only allow valid columns 6. If Col_No < 1 Or Col_No > 256 Then Exit Function 7. 8. If Col_No < 27 Then 'Single letter 9. xl_Col = Chr(Col_No + 64) 10. Else 'T wo letters 11. xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & _ 12. Chr(((Col_No - 1) Mod 26) + 1 + 64) 13. End If 14. 15. End Function 16.

17. 18.

'example usage: sColName = xl_Col(7)

VB Code:
1. Function xl_ColNo(Col_Name) As Integer 2. 'returns an Excel column number from its name (e.g.: col_name "AA" returns 27) 3. 'by Si_the_geek (VBForums.com) 4. 5. Col_Name = UCase(Trim(Col_Name)) 6. Select Case Len(Col_Name) 7. Case 1: xl_ColNo = Asc(Col_Name) - 64 8. Case 2: xl_ColNo = ((Asc(Left(Col_Name, 1)) - 64) * 26) _ 9. + (Asc(Right(Col_Name, 1)) - 64) 10. End Select 11. 12. End Function 13. 14. 'example usage: 15. iColNo = xl_ColNo("Z")

. Last edited by si_the_geek; Nov 4th, 2010 at 04:47 AM. Reason: added link to Excel 2003 constants Mar 8th, 2006, 05:34 AM si_the_geek Super Moderator Frequently Asked Questions Does the user need Excel installed? Yes (how can you automate something that isn't there?), and you cannot install it without them having a valid licence for it. Join Date: Jul 02 Location: Bristol, UK Posts: 33,133

#14

What library version number should I use? To be honest any which you have installed will normally be fine, however if you want to use "new" functionality that has been added in newer versions of Excel, you need to select the one which provides that functionalility. Since the introduction of Excel 95, the versions haven't been obvious (until then there were "proper" version numbers like 5.0). The reference that you use in your program reflects the internal version

number, rather than the displayed number. The version numbers are:

7.0 - Excel 95 8.0 - Excel 97 9.0 - Excel 2000 10.0 - Excel XP 11.0 - Excel 2003

How do I work with a copy of Excel that is already open? This will connect the application object to an open copy of Excel: VB Code:
1. Set oXLApp = GetObject(,"Excel.Application")

Please note that there is no way to specify which open copy of Excel to work with, so if there is more than one open you cannot predict which one will be referenced. Note also that if there are no instances of Excel running, this line of code will cause an error.