Exporting Data to Excel and append or add data

Exporting Data to Excel and append or add data

Post by David Powel » Thu, 23 Nov 2000 16:23:40



What I have:
I'am exporting my data to Excel.
The code is generating a new Excel Workbook.
and copying the data to this New sheet.

What I need:
Append New Export to "AppendedData.xls". OR Create the workbook if it does
not exist.
Example:
If Start a Data collection all Over again.

I have new data to append to the Excel file.
If the the Excel sheet already Exists OR has data in it..  The I need to
append data at the first open cell in "Column A"

Current  Constants:...........................
Data will ALWAYS reside from Columns:
A1..AO  <--NOT ZERO but letter "O"

Record Count will vary.

New Constants:....................
The spreadsheet to ALWAYS append Data into,.....I NEED CALLED

I Need the Spreadsheet created if it does not exist under..............
c:\Data\       <   folder

'   start code
'Export routine 03/23/00 MGW
  Public Function Export()
  Screen.MousePointer = vbHourglass
    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 the Working database
   strDB = "c:\DATA\MYDaily.mdb"
            ' Open connection to the database
   cnt.Open "Provider=Microsoft.Jet.OLEDB.3.51;" & _
        "Data Source=" & strDB & ";"
            ' Open recordset based on query
   rst.Open "Select * From qryExport", cnt
            ' Create an Excel worksheet and add a workbook
   Set xlApp = CreateObject("Excel.Application")
   Set xlWb = xlApp.Workbooks.Add
   Set xlWs = xlWb.Worksheets("sheet1")
           ' Display Excel and give user control of Excel
   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(Left$(xlApp.Version, 1)) > 8 Then
     'EXCEL 2000: Use CopyFromRecordset
   ' Copy the recordset to the worksheet, starting in cell A2
   xlWs.Cells(2, 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
   Screen.MousePointer = vbArrow
End Function

' end code.........................

Thanks
fordraiders

 
 
 

Exporting Data to Excel and append or add data

Post by Steve Lia » Fri, 24 Nov 2000 15:45:08


Hi David,

You can first judge the existance of the Excel file by using the FileExists
of the filesystem object.

If you have the excel file, then you can open it as a datasource in ADO:

    cn.Open "provider=microsoft.jet.oledb.4.0;Data
Source=c:\test\test.xls;Extended Properties=Excel 8.0;"
    rs.Open "select * from [sheet1$]", cn, adOpenKeyset, adLockOptimistic,
adCmdText

If you don't have the file, you can use your method to go on.

Some useful resource:
Q247412, "INFO: Methods for Transferring Data to Excel from Visual Basic"
http://support.microsoft.com/support/kb/articles/q247/4/12.asp

Steve

 
 
 

1. Exporting data to excel file /overwrite existing data.

Hi:
I have created a local package through DTS. Evvery time pacakge is
executed, it is supposed to perform following steps.
1)Delete existing data in the table ,
2)Import data from another databse to the above table.
3)Perform Updates.
4)Export data to an excel file through a select statement ,every 4
hours.
Everything works fine, except step # 4.It looks for file, then instead
of overwriting existing data, it appends the new data to existing data.
This process is suppose to run ever 4 hours,so this job has to be
automated.
Can anyone help me figure out how to export data through DTS and
overwrite existing Excel file and create a new file or just erase the
current data in excel and export the new data.
Thanks in advance
B M

Sent via Deja.com http://www.deja.com/
Before you buy.

2. SQL 6.5 EE and Clustering known anomalies

3. append exported data in flat file

4. add row with autonumber -> not the same as stored on server

5. Truncated data in DTS export to Excel

6. Text Field wrap on web page

7. Exporting data to excel with VB and SQL

8. Cognos Vs SAS Vs ?? against SQL Server/Essbase

9. exporting data from sqlserver to specific cells in excel

10. Export large data to excel sheet

11. How to export SQL Server 2000 data to Excel via ASP

12. exporting data into excel worksheet

13. Export data from SQL to Excel using DTS Package