Solved

How to Setup Excel sheet object in visual basic 6?

Posted on 2014-02-11
14
943 Views
Last Modified: 2014-02-27
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
Comment
Question by:elm9999
  • 7
  • 3
  • 2
14 Comments
 
LVL 3

Expert Comment

by:Conner Turner
ID: 39851515
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
 

Author Comment

by:elm9999
ID: 39851550
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
 

Author Comment

by:elm9999
ID: 39851636
If I use:

oSheet.PageSetup.Orientation = 2

Works fine but if I use xlLandscape instead of 2, the error occurs!
0
 
LVL 3

Expert Comment

by:Conner Turner
ID: 39851727
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
 

Author Comment

by:elm9999
ID: 39851791
Yea it works but the thing is that I have to border too. and center text in the cells.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39851793
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:elm9999
ID: 39851794
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
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39851822
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
 

Author Comment

by:elm9999
ID: 39851854
Thank you Qlemo,

Sometimes I hate Microsoft :)
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39851861
Sometimes? Lucky man ...
0
 

Author Comment

by:elm9999
ID: 39851911
All right Q I didn't know.

Can I change it?
0
 

Author Comment

by:elm9999
ID: 39851935
OK,

Thanks.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now