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.QuitEnd Sub
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
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.
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 .
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.
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.
You can use a With statement based on the active sheet to replicate the Macro within VBA:
Open in new window
You can see all functions an uses of the PageSetup function on the msdn website here:
[MSDN]