Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Export table to Excel weekly and start on first blank row

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
nicoleheritage
Asked:
nicoleheritage
  • 9
  • 6
  • 2
  • +3
1 Solution
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
flavoCommented:
You can use ADO to connect to the Excel spread sheet and use that to write to the xls
0
Technology Partners: We Want Your Opinion!

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!

 
rockiroadsCommented:
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
 
flavoCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
nicoleheritageAuthor Commented:
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
 
rockiroadsCommented:
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
 
nicoleheritageAuthor Commented:
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
 
rockiroadsCommented:
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
 
nicoleheritageAuthor Commented:
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
 
rockiroadsCommented:
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
 
nicoleheritageAuthor Commented:
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
 
rockiroadsCommented:
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
 
nicoleheritageAuthor Commented:
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
 
rockiroadsCommented:
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
 
nicoleheritageAuthor Commented:
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
 
rockiroadsCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jadedataMS Access Systems CreatorCommented:
Was anyone going to mention the copyfromrecordset method on this one?
0
 
moduloCommented:
PAQed - no points refunded (of 500)

modulo
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now