[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

Solved

How to Setup Excel sheet object in visual basic 6?

Posted on 2014-02-11

1 solution

Medium Priority

1,102 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

Comment

Question by:elm9999

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

14 Comments

LVL 3

Expert Comment

by:Conner Turner

ID: 398515152014-02-11

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]

Author Comment

by:elm9999

ID: 398515502014-02-11

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?

Author Comment

by:elm9999

ID: 398516362014-02-11

If I use:

oSheet.PageSetup.Orientation = 2

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

LVL 3

Expert Comment

by:Conner Turner

ID: 398517272014-02-11

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

Author Comment

by:elm9999

ID: 398517912014-02-11

Yea it works but the thing is that I have to border too. and center text in the cells.

LVL 71

Expert Comment

by:Qlemo

ID: 398517932014-02-11

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.

Author Comment

by:elm9999

ID: 398517942014-02-11

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.

LVL 71

Accepted Solution

by:Qlemo

Qlemo earned 2000 total points

ID: 398518222014-02-11

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.

Author Comment

by:elm9999

ID: 398518542014-02-11

Thank you Qlemo,

Sometimes I hate Microsoft :)

LVL 71

Expert Comment

by:Qlemo

ID: 398518612014-02-11

Sometimes? Lucky man ...

Author Comment

by:elm9999

ID: 398519112014-02-11

All right Q I didn't know.

Can I change it?

Author Comment

by:elm9999

ID: 398519352014-02-11

OK,

Thanks.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Connect to SAP R/3 and call custom FM from MS Excel

Article by: ExcelGuide

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.

VB Scripts and UAC elevation

Article by: Vikas

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…

Building Probability Models in Excel Part 6: Investment Modeling Using a Log-Normal Distritribution

Video by: Toby

The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

Creating an Efficient Dashboard in Excel

Video by: Katie

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…