Solved

Export table to Excel weekly and start on first blank row

Posted on 2004-04-08
22
274 Views
Last Modified: 2008-02-01
A table (TblExport) in my database needs to be exported to Excel weekly.  The code I have now wants to add a new worksheet every time it is exported.  Is there a way to have the data go on the same sheet every week to have it continuous?  What code would I use?  Or is there an easier/better way to achieve the same results?
0
Comment
Question by:nicoleheritage
  • 9
  • 6
  • 2
  • +3
22 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
If you want to append an existing Excel sheet with new data Im not sure if

DoCmd.TransferSpreadsheet would do the job

have you considered Excel automation

i.e. create an Excel object

then find next available row

then add your new tblExport

then save

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
e.g.

'create a reference to Microsoft Excel object in Tools/References

'Define variable
Private m_objExcel As Excel.Application

'Create excel object
Set m_objExcel = New Excel.Application

'Open document
m_objExcel.Workbooks.Open FileName:=sFile, Notify:=False



0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
You can use ADO to connect to the Excel spread sheet and use that to write to the xls
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, posted before finished, sorry

'To get last available row

    iLastRowNum = m_objExcel.Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1

'To write to a cell

m_objExcel.Cells(iRow, iCol).Value = ...

'To write a range

m_objExcel.Range(m_objExcel.Cells(iRow1, iCol1), m_objExcel.Cells(iRow2, iCol2)).Value = ...



with regards to your table, you will probably have to create a recordset
and a row counter
go thru the recordset, writing to this file and incrementing row counter for each new record
you decide what field goes on what column


'to save
    m_objExcel.DisplayAlerts = False
    m_objExcel.Workbooks(1).Save

'To close
    m_objExcel.Workbooks.Close
    m_objExcel.Quit



0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
Sample conneciton

Dim rst As ADODB.Recordset
Dim cn  As ADODB.Connection
Dim fld As ADODB.Field

'open the connection to excel
Set cn = New ADODB.Connection
'Set provider
cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
' Connection string
cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=C:\myExcelFile.xls;"
' Open the connection
cn.Open
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Check this link
Here you could direct where to place the data; Goto Copying an Entire Recordset

Using Automation to Transfer Data to Microsoft Excel
http://support.microsoft.com/?kbid=210288

0
 

Author Comment

by:nicoleheritage
Comment Utility
Here is the code I have and it is getting hung up on the line *'d -- saying "Application-defined or object-defined error".

Any suggestion?  Tried a couple of variations, but same error.  

Private Sub Export_Click()
On Error GoTo Err_Export_Click

    Dim stDocName As String
    Dim m_objExcel As Excel.Application
    Dim rsExport As DAO.Recordset
    Dim dbService As DAO.Database
    Dim strSQL As String
    Dim lngRow As Long, intCol As Integer
    Dim intLastRow As Integer
    Dim objWSheet As Object
       
    Set dbService = DBEngine.Workspaces(0).Databases(0)
   
    strSQL = "SELECT * FROM [TblExport]"
   
    Set rsExport = dbService.OpenRecordset(strSQL, dbOpenDynaset)
   
    Set m_objExcel = New Excel.Application
   
    sfile = "FileName"
   
    m_objExcel.Workbooks.Open sfile, False
   
    Set objWSheet = m_objExcel.ActiveWorkbook.Sheets("Sheet1")
   
    For lngRow = 0 To 655536
        If m_objExcel.ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = "" Then
            intLastRow = lngRow
            Exit For
        End If
    Next lngRow
   
    For lngRow = 0 To rsExport.RecordCount - 1
        For intCol = 0 To rsExport.Fields.Count - 1
*******             m_objExcel.Cells(intLastRow + 1, intCol).Value = rsExport.Fields(intCol)  ********
            intLastRow = intLastRow + 1
        Next intCol
    Next lngRow
   
    m_objExcel.DisplayAlerts = False
    m_objExcel.Workbooks(1).Save
   
    m_objExcel.Workbooks.Close
    m_objExcel.Quit
       

Exit_Export_Click:
    Exit Sub

Err_Export_Click:
    MsgBox Err.Description
    Resume Exit_Export_Click
End Sub
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
did you create a reference to the Excel object

go to tools/references and pick Microsoft Excel v9.0 Object Library


also, I see where you are trying to get the last row
did this not work

iLastRowNum = m_objExcel.Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1

0
 

Author Comment

by:nicoleheritage
Comment Utility
Yes, I created that reference.  I'm assuming 10.0 Object Library will do the same thing.......

It didn't work originally, but I couldn't remember the error message - so I just tried putting it back in and it works.  Still doesn't solve the problem with this loop though:

For lngRow = 0 To rsExport.RecordCount - 1
        For intCol = 0 To rsExport.Fields.Count - 1
             m_objExcel.Cells(intLastRow + 1, intCol).Value = rsExport.Fields(intCol)
            intLastRow = intLastRow + 1
        Next intCol
    Next lngRow

What am I missing?

Thanks,
Nicole
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, with this, I forgot to add, what is the name of your sheet? this example uses Sheet1, you need to replace it with your sheet name

iLastRowNum = m_objExcel.Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1


I think the problem with your loop is, you start with zero
you cannot possibly have a row and column starting from zero

maybe you should change your starting point to 1 - do you see where Im getting at (first set would set to cell 0,0)

For lngRow = 1 To rsExport.RecordCount - 1
        For intCol = 1 To rsExport.Fields.Count - 1
             m_objExcel.Cells(intLastRow + 1, intCol).Value = rsExport.Fields(intCol)
            intLastRow = intLastRow + 1
        Next intCol
    Next lngRow
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:nicoleheritage
Comment Utility
I never changed the name of the sheet, so it is named "Sheet1"

There are no more errors in the code.  It runs just fine.

BUT the data is not in the excel file.  The Excel sheet has date/time last modified as if it was just modified, but the whole thing is completely empty.

Any suggestions?

Thanks,
Nicole
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I noticied that sFile is pointing to "FileName"
is that a valid excel file? you should be pointing to a valid excel file eg. C:\myExcel.xls

also in the open, dont specify the False after, just leave as m_objExcel.Workbooks.Open sFile

also you could try seeing what rsExport.Fields(intCol) is

do debug.print  intCol, rsExport.Fields(intCol)
then look in the intermediate window to see if data is coming out


also comment out the DisplayAlerts, lets see if any messages come up



0
 

Author Comment

by:nicoleheritage
Comment Utility
Yes, the file name is mapped out correctly, but it contains the company name, so I chose to delete it for posting.

Changed those couple of things.  By commenting on the alerts, I saw that it was overriding the excel file, not updating it and the default was on no, so that is why the file never changed.  Not sure what to do about that.  It seems to think the file is read only even though I checked the properties and it is not.

Then when I did open the excel file it had data only going down the diaganol (A1, B2, C3, etc.) and it only had the first couple of fields.  But I think I fixed it by moving it around to this:

For lngRow = 1 To rsExport.RecordCount - 1
        For intCol = 1 To rsExport.Fields.Count - 1
            m_objExcel.Cells(intLastRow + 1, intCol).Value = rsExport.Fields(intCol)
        Next intCol
        intLastRow = intLastRow + 1
    Next lngRow


Thanks,
Nicole

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, gotcha about company name, no worries

regarding the file open, there is a readonly flag as one of the parameters

e.g.
    m_objExcel.Workbooks.Open FileName:=sFile, Notify:=False, ReadOnly:=False

you could try that


in your look for the recordset, do rsExport.Fields(intCol) supposed to start from zero or one to get the first data item

if it starts from zero then I think you should do this

For lngRow = 0 To rsExport.RecordCount - 1
        For intCol = 0 To rsExport.Fields.Count - 1
            m_objExcel.Cells(intLastRow + 1, intCol+1).Value = rsExport.Fields(intCol)
        Next intCol
        intLastRow = intLastRow + 1
    Next lngRow

basically, I just incremented your intLastRow+1 and intCol by 1

if rsExport.Fields starts from 1, then fine leave it as is - suggest you try the above though, only because I saw you do RecordCount-1, that tend to be that it starts from zero
0
 

Author Comment

by:nicoleheritage
Comment Utility
Okay.  Those changes were made without running into errors.  We're getting so close.  The only problem is now that it is only exporting the first record -- a step above not exporting at all I believe.  There are 7 records in the table.  The # of records in the table will vary each week.

Any ideas?

Thanks a ton!
Nicole
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
maybe you need to go to the next field

i.e. rsExport.Next  (or whatever the syntax is)

put this where you increment intLastRow


0
 

Author Comment

by:nicoleheritage
Comment Utility
when I step through the code & hover over

For lngRow = 0 To rsExport.RecordCount - 1

it says that rs.export.recordcount = 1

Does that indicate that something bigger is going on....that it is only recognizing one record even though there are 7 in the table??

Thanks,
Nicole
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
the problem with recordcount, you have to go to the last field to get the accrurate count

e.g.

rsExport.MoveLast (or whatever the syntax is)
then go back to the first record (MoveFirst)
then do your loop

the alternative is

while rsExport.EOF = False
 ....


so we do away with recordcount and use the End Of File flag to determine whether we continue or not
remember to put in the next otherwise you will be in a infinite loop!

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Nicole,

Try this

Do Until rsExport.EOF
    For intCol = 0 To rsExport.Fields.Count - 1
 
            m_objExcel.Cells(intLastRow, intCol+1).Value = rsExport.Fields(intCol)
 
    Next intCol
    rsExport.MoveNext
    intLastRow = intLastRow + 1
Loop
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Was anyone going to mention the copyfromrecordset method on this one?
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
PAQed - no points refunded (of 500)

modulo
Community Support Moderator
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now