• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1219
  • Last Modified:

How to Setup Excel sheet object in visual basic 6?

Hi guys,

I don't know to much about Excel objects, my code works good but I only need to do the following:

How to:
Set the sheet = landscape
set Horizontal alignment on a cell = Center
set vertical alignment on a cell = Center
Set borders on my range
Set font and size.

This is my code:

Sub ExportaaExcel()
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    Dim nRenglon As Integer
    Dim nContador As Integer
    Dim cCambio As String
    Dim nConsecutivo As Integer
    
    cpNombreArchivo = cpNombreArchivo & "_" & cFecha & "_" & cHora & ".xls"
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Add
       
    Set oSheet = oBook.Worksheets(1)
    Select Case npOpcionReporte
        Case 1
            oSheet.Range("A1").Value = "CONSEC"
            oSheet.Range("B1").Value = "FECHA RECIBIDO"
            oSheet.Range("C1").Value = "NUMERO EXPEDIENTE"
            oSheet.Range("D1").Value = "AUTORIDAD SOLICITA"
            oSheet.Range("E1").Value = "PROMOVENTE"
            oSheet.Range("F1").Value = "SOLICITUD"
            oSheet.Range("G1").Value = "SEGUIMIENTO"
            nRenglon = 2
            nConsecutivo = 1

            Do While Not rsTransacciones.EOF
                oSheet.Range("A" & nRenglon).Value = nConsecutivo
                oSheet.Range("B" & nRenglon).Value = rsTransacciones!Fecha_recibido
                oSheet.Range("C" & nRenglon).Value = rsTransacciones!Num_expediente
                oSheet.Range("D" & nRenglon).Value = rsTransacciones!Autoridad_solicita
                oSheet.Range("E" & nRenglon).Value = rsTransacciones!Promovente
                oSheet.Range("F" & nRenglon).Value = rsTransacciones!Solicitud
                oSheet.Range("G" & nRenglon).Value = rsTransacciones!Seguimiento
                nRenglon = (nRenglon + 1)
                nConsecutivo = (nConsecutivo + 1)
                rsTransacciones.MoveNext
            Loop

            oSheet.Columns("A:G").WrapText = True
            oSheet.Range("A1:L1").Font.Bold = True
            oSheet.Range("A1:L1").Font.Bold = True
            oSheet.Columns("A:A").Columnwidth = 5
            oSheet.Columns("B:B").Columnwidth = 10
            oSheet.Columns("C:C").Columnwidth = 15
            oSheet.Columns("D:D").Columnwidth = 25
            oSheet.Columns("E:E").Columnwidth = 25
            oSheet.Columns("F:F").Columnwidth = 35
            oSheet.Columns("G:G").Columnwidth = 28
            oSheet.Range("A1:B1").Font.Bold = True
    End Select
    oBook.SaveAs "C:\Reportes\" & cpNombreArchivo
    oExcel.Quit
End Sub

Open in new window

0
elm9999
Asked:
elm9999
  • 7
  • 3
  • 2
1 Solution
 
Conner TurnerSenior DirectorCommented:
Hello There,

You can use a With statement based on the active sheet to replicate the Macro within VBA:

With ActiveSheet.PageSetup
    .Orientation = xlLandscape
End With

Open in new window


You can see all functions an uses of the PageSetup function on the msdn website here:
[MSDN]
0
 
elm9999Author Commented:
If I use this:

With ActiveSheet.PageSetup
    .Orientation = xlLandscape
End With

Appears this error:  "variable not defined"  ActiveSheet.

If I use this:

With oSheet.PageSetup
    .Orientation = xlLandscape
End With


Appears this error:  "object required"  Orientation = xlLandscape

So, do I have to activate it first?
0
 
elm9999Author Commented:
If I use:

oSheet.PageSetup.Orientation = 2

Works fine but if I use xlLandscape instead of 2, the error occurs!
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Conner TurnerSenior DirectorCommented:
Hmm,

I suspense it make s no difference what you use , a s long as you don't need to relay or print it back to the sheet it should all be fine !

HTH,

Conner
0
 
elm9999Author Commented:
Yea it works but the thing is that I have to border too. and center text in the cells.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Since you use late binding (CreateObject("Excel.Application")), you do not have access to Excel constants. I don't think it is worth it, but if you want to have them available, instructions for including them can be found at http://support.microsoft.com/kb/112671 .

The other way is to use early binding, by adding a reference to Excel, allowing direct access to Excel objects and constants. Helpful instructions are posted at http://www.vbforums.com/showthread.php?391665-Tutorial-FAQ-How-to-Automate-Excel-from-VB6-(or-VB5-VBA) .

Using Active* isn't recommended. Whenever possible, use the var pointing to the object directly (i.e. oSheet). Using Active* tends to break on user interaction, e.g. accidental clicks or keystrokes.
0
 
elm9999Author Commented:
I tried this function:

Sub RedOutlineCells()
    Dim rng As Range

    Set rng = Range("A1:F20")

    With rng.Borders
        .LineStyle = xlContinuous
        .Color = vbRed
        .Weight = xlThin
    End With
End Sub

but the is like the vb6 doesn't recognize some object instructions.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You'll need to prefix an object reference. Range needs e.g. a worksheet object it can get applied against:
   Set rng = oSheet.Range("A1:F20")

Open in new window

Directly using Range only works in Excel VBA.
0
 
elm9999Author Commented:
Thank you Qlemo,

Sometimes I hate Microsoft :)
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sometimes? Lucky man ...
0
 
elm9999Author Commented:
All right Q I didn't know.

Can I change it?
0
 
elm9999Author Commented:
OK,

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now