VBA code in Excel..how do I do this?

How do I do this in right way? No it doesent work...

VB Code:

Dim xlApp As Excel.Application
Dim xlBook As Object
Dim xlSheet As Object
 
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    
xlApp.ScreenUpdating = False
    
    With xlSheet
        .PageSetup.Orientation = xlLandscape
        .PageSetup.LeftMargin = xlApp.Application.InchesToPoints(0.196850393700787)
        .PageSetup.RightMargin = xlApp.Application.InchesToPoints(0.196850393700787)
        .PageSetup.TopMargin = xlApp.Application.InchesToPoints(0.78740157480315)
        .PageSetup.BottomMargin = xlApp.Application.InchesToPoints(0.78740157480315)
        .Range("A1:P1").Select
        .Selection.MergeCells = True
        .Rows("1:1").RowHeight = 36.75
        .ActiveCell.FormulaR1C1 = "TestHeader"
        .Selection.Font.Size = 14
        .Selection.Font.Bold = True
        .Selection.HorizontalAlignment = xlCenter
        .Selection.VerticalAlignment = xlCenter
        .Range("A2").Select
        .Application.Visible = True
 End With
 
 
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

It looks ok to me, except this section:

VB Code:

.Range("A1:P1").Select
        .Selection.MergeCells = True
        .Rows("1:1").RowHeight = 36.75
        .ActiveCell.FormulaR1C1 = "TestHeader"
        .Selection.Font.Size = 14
        .Selection.Font.Bold = True
        .Selection.HorizontalAlignment = xlCenter
        .Selection.VerticalAlignment = xlCenter

..which should ideally be as below (to eliminate Selection objects, which can change if the user is working with Excel too):

VB Code:

.Range("A1:P1").MergeCells = True
        .Rows("1:1").RowHeight = 36.75
        With .Range("A1:P1")
          .FormulaR1C1 = "TestHeader"   'should this be Value instead of FormulaR1C1?
          .Font.Size = 14
          .Font.Bold = True
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlCenter
        End With

Anyway, what is the problem you are having? "No it doesent work..." tells us virtually nothing.

Thanks si_the_geek!

Yes, this should this be Value and not FormulaR1C1.

VB Code:

.FormulaR1C1 = "TestHeader"

You wrote:

Code:

---

Dim xlApp As Excel.Application
Dim xlBook As Object
Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)

---

I would have set Option Explicit, and done the following:

Code:

---

Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Sheets(1)

---

I don't know if this makes any difference or not ...

There is a reason to define them as Object, it means that you can work with multiple versions of Excel (ie: your users do not need the same version as you). I presume that Pirre001 intends to also change the Excel.Application to Object later too.

Oh, and instead of "as Workbook" you should have used "as Excel.Workbook", else any other classes called Workbook could be used. (same for "as Worksheet")