We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
12 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to Setup Excel sheet object in visual basic 6?
Posted on 2014-02-11
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:
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
[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
-
7
3
2
14 Comments
Active 5 days ago
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?
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?
Active 5 days ago
If I use:
oSheet.PageSetup.Orientati
Works fine but if I use xlLandscape instead of 2, the error occurs!
oSheet.PageSetup.Orientati
Works fine but if I use xlLandscape instead of 2, the error occurs!
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
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
Active 5 days ago
Yea it works but the thing is that I have to border too. and center text in the cells.
Active today
Since you use late binding (CreateObject("Excel.Applic
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.
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.
Active 5 days ago
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.
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.
Active today
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")
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses
Course of the Month12 days, 9 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.