I have the following code to populate an excel spreadsheet from vb6 and access database. It works great except that when it reads from the recordset and populates the spreadsheet, I would like it to populate every other line in the spreadsheet. I can't seem to see anywhere in the code where that might be handled. Any help would be greatly appreciated. - Private Sub lblGroundScheduleExcel_Click()
-
Dim cnt As New ADODB.Connection
-
Dim rst As New ADODB.Recordset
-
-
Dim xlApp As Object
-
Dim xlWb As Object
-
Dim xlWs As Object
-
-
-
Dim recArray As Variant
-
-
Dim strDB As String
-
Dim fldCount As Integer
-
Dim recCount As Long
-
Dim iCol As Integer
-
Dim iRow As Integer
-
-
' Set the string to the path of your Northwind database
-
strDB = "c:\scheduling\AAGTC_Scheduling.mdb"
-
-
' Open connection to the database
-
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=" & strDB & ";"
-
-
' Open recordset based on Orders table
-
rst.Open "Select Date, Unit, P_O_C, Mission, Vehicle_Qty, Personnel_Daily_Report, [Range Areas], Facility_Useage_For_Daily_Report, Daily_Ordinance, Comments From qryDailyGroundScheduleReport", cnt
-
-
' Create an instance of Excel and add a workbook
-
Set xlApp = CreateObject("Excel.Application")
-
Set xlWb = xlApp.Workbooks.Add("C:\Scheduling\Excel\Deployment_Activity1.xls")
-
Set xlWs = xlWb.Worksheets("2007")
-
-
' Display Excel and give user control of Excel's lifetime
-
xlApp.Visible = True
-
xlApp.UserControl = True
-
-
' Copy field names to the first row of the worksheet
-
' fldCount = rst.Fields.Count
-
' For iCol = 1 To fldCount
-
' xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
-
'Next
-
-
' Check version of Excel
-
If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
-
'EXCEL 2000 or 2002: Use CopyFromRecordset
-
-
' Copy the recordset to the worksheet, starting in cell A2
-
xlWs.Cells(3, 1).CopyFromRecordset rst
-
'Note: CopyFromRecordset will fail if the recordset
-
'contains an OLE object field or array data such
-
'as hierarchical recordsets
-
-
Else
-
'EXCEL 97 or earlier: Use GetRows then copy array to Excel
-
-
' Copy recordset to an array
-
recArray = rst.GetRows
-
'Note: GetRows returns a 0-based array where the first
-
'dimension contains fields and the second dimension
-
'contains records. We will transpose this array so that
-
'the first dimension contains records, allowing the
-
'data to appears properly when copied to Excel
-
-
' Determine number of records
-
-
recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
-
-
-
' Check the array for contents that are not valid when
-
' copying the array to an Excel worksheet
-
For iCol = 0 To fldCount - 1
-
For iRow = 0 To recCount - 1
-
' Take care of Date fields
-
If IsDate(recArray(iCol, iRow)) Then
-
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
-
' Take care of OLE object fields or array fields
-
ElseIf IsArray(recArray(iCol, iRow)) Then
-
recArray(iCol, iRow) = "Array Field"
-
End If
-
Next iRow 'next record
-
Next iCol 'next field
-
-
' Transpose and Copy the array to the worksheet,
-
' starting in cell A2
-
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
-
TransposeDim(recArray)
-
End If
-
-
' Auto-fit the column widths and row heights
-
xlApp.Selection.CurrentRegion.Columns.AutoFit
-
xlApp.Selection.CurrentRegion.Rows.AutoFit
-
-
' Close ADO objects
-
rst.Close
-
cnt.Close
-
Set rst = Nothing
-
Set cnt = Nothing
-
-
' Release Excel references
-
Set xlWs = Nothing
-
Set xlWb = Nothing
-
-
Set xlApp = Nothing
-
-
End Sub
-
0 3601 Sign in to post your reply or Sign up for a free account.
Similar topics
by: Allison Bailey |
last post by:
Hi Folks,
I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....
I would like to open an existing MS Excel spreadsheet...
|
by: Otie |
last post by:
I found the following under the GetObject help notes and in the
example for GetObject:
"This example uses the GetObject function to get a reference to a
specific Microsoft Excel worksheet...
|
by: Matthew Wieder |
last post by:
I have the following requirements:
Build a stand-alone C# application that asks the user to click in a cell
in an Excel spreadsheet, and then displays the address of that cell in
the C#...
|
by: pmud |
last post by:
Hi,
I need to use an Excel Sheet in ASP.NET application so that the users can
enter (copy, paste ) large number of rows in this Excel Sheet. Also, Whatever
the USER ENETRS needs to go to the...
|
by: Howard Kaikow |
last post by:
There's a significant problem in automating Excel from VB .NET.
Reminds me of a problem I encountered almost 3 years ago that was caused by
the Norton Auntie Virus Office plug-in.
Can anybody...
|
by: Anthony |
last post by:
To me, creating Excel 2003 spreadsheets programmatically via VB.NET hasn't
really changed since the days of VB6.
That is, I'd do something similar to this
Code:
Dim ExcelApp As...
|
by: Alain \Mbuna\ |
last post by:
Hi everybody.
In my program I have some data that is calculated after some input from the
user. I have written some code that opens an Excel workbook, with 5
worksheets and the calculated data...
|
by: alexia.bee |
last post by:
Hi all,
In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.
here is a snipcode
public...
|
by: Doug Glancy |
last post by:
I got the following code from Francesco Balena's site, for disposing of Com
objects:
Sub SetNothing(Of T)(ByRef obj As T)
' Dispose of the object if possible
If obj IsNot Nothing AndAlso...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |