Solved

Export table to Excel weekly and start on first blank row

Posted on 2004-04-08
22
302 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
ID: 10783059
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
ID: 10783136
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
ID: 10783148
You can use ADO to connect to the Excel spread sheet and use that to write to the xls
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

Expert Comment

by:rockiroads
ID: 10783173
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
ID: 10783175
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10783309
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
ID: 10784566
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
ID: 10784607
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
ID: 10785056
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
ID: 10785220
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
 

Author Comment

by:nicoleheritage
ID: 10785514
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
ID: 10786862
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
ID: 10787560
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
ID: 10787598
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
ID: 10791805
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
ID: 10791861
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
ID: 10792182
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
ID: 10792253
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10792300
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
ID: 11152180
Was anyone going to mention the copyfromrecordset method on this one?
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 11187100
PAQed - no points refunded (of 500)

modulo
Community Support Moderator
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
On Key Down Access 2010 6 41
MS Access 2012, Split Form 2 39
Run an action on recently added records to a table 13 61
how to insert parameter value in table 2 21
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

856 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