Formatting Excel Spreadsheet from Access 1

[ScottXJ]

Hello,

The Access 2000 database currently used by my team has several reports that are run on a daily basis and then posted to our corporate intranet site. The process used at this time is basically as follows:

1. Macro creates temp table based on query.
2. Temp table data is exported to Excel spreadsheet
3. Spreadsheet data is imported into formatted, Excel spreadsheet/template
4. Formatted spreadsheet is posted to web site

What I would like to do is eliminate the process of having to copy the data from the unformatted Excel spreadsheet and pasting into the formatted template. It would be more efficient to simply dump the data into an already formatted spreadsheet. All of these current procedures currently run as code on the Click event of a command button.

Apologies for the long post.

Thanks in advance for any help,

Scott.

 

[xlbo]

Rather than exporting the table, use ADO / DAO to take the query results into teh recordset and dump them into the specified excel file - sample code but:

Sub Export_To_Excel()

Dim strSql As String
Dim dBase As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim uRow As Integer
Dim xlApp As Object
Dim Sht As Object
 
    DoCmd.SetWarnings False
    '[COLOR=green] 'setup query to get all records from a table[/color]
    strSql = "SELECT * FROM [TWPW AGM];"

    Set dBase = CurrentDb()
    Set rs = dBase.OpenRecordset(strSql, dbOpenDynaset)
    '[COLOR=green] 'Create Excel object[/color]
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add
    xlApp.Visible = True
    Set Sht = xlApp.ActiveWorkbook.Sheets(1)
        uRow = 1
       With rs
        '[COLOR=green] ' Loop through the field names[/color]

        For i = 0 To .Fields.Count - 1
            Sht.Cells(uRow, i + 1).Value = rs.Fields(i).Name
        Next i
        uRow = 2
       End With
        '[COLOR=green] ' Loop through the records and copy them to worksheet.[/color]
[b]'This bit can be replaced by the COPYFROMRECORDSET function[/b]        
        Do Until rs.EOF

            For i = 0 To rs.Fields.Count - 1
                Sht.Cells(uRow, i + 1).Value = rs(i)
            Next i
            rs.MoveNext
            uRow = uRow + 1

        Loop
        '[COLOR=green] 'Other stuff[/color]
ActiveWorkbook.SaveAs "Full Path & Name go here"
'[COLOR=green] 'Clear variables[/color]
Set Sht = Nothing
xlApp.Quit
Set xlApp = Nothing
Set rs = Nothing
Set dBase = Nothing
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ScottXJ]

Geoff:

I apologize for not expressing my thanks promptly after your submission of this code. I have created a module using the code you provided. The process seems to work fine, however, when I attempt to open the newly created spreadsheet, my PC hangs for some time (almost 5 min) before the spreadsheet actually opens. I thought that there may be some issue with the code not exiting Excel properly? In addition, I would like to know if it would be possible to import the recordset into a formatted template and then saving the template as a new file name rather than creating a new spreadsheet each time as your code example does? The reason for this is that we have a corporate standard template and I wanted to eliminate the process of having to import data into the template.
Any help would be appreciated.

Thanks,
Scott.

 

[ScottXJ]

Geoff:

I discovered that if I close my Access application, the newly created spreadsheet opens immediately(?). Also, the date fields do not show up properly when dumping the recordset but are not affected when exporting the query results as an Excel spreadsheet. I'm not sure if there is some additional code that will be required to deal with the date fields. I can post the spreadsheets if you would like to see them in more detail.

Thanks again,
Scott.

 

[xlbo]

Sorry for not replying sooner - been on holiday. Back in work tomorrow so I'll have a closer look then but in terms of dumping the data to a template - no probs - just replace:

xlApp.Workbooks.Add

with

xlApp.workbooks.open filename:="FullPathAndFileNameGoesHere.xls"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ScottXJ]

Hi Geoff:

Thanks for the quick reply. I tested out the code change you suggested and the process does work and actually eliminates the problems I was experiencing with the date fields displaying properly. Unfortunately, there is still one remaining problem as well as an additional issue that seems to have popped up. I am still having problems opening the new spreadsheet after the procedure has finished. It takes quite a while for the new spreadsheet to open (approx. 3 min). However, if I close Access, the spreadsheet can be opened immediately (no hang). The new issue that has shown up has to do with the table column names showing up in the template. When the dataset is copied to the Excel spreadsheet template using the code change you indicated above, the column names from the table now appear in the first row of the new spreadsheet. Is there any way to suppress this or have the data start on a specified cell range within Excel?

I'm going to continue to plug away and I will post any new findings.

Thanks again Geoff,

Scott.

 

[ScottXJ]

Hi Geoff:

Just an update - I was able to get the data to display properly in the new spreadsheet and eliminate the table column names from appearing. I just had to test different integers for the uRow variable. This only leaves me with the issue of how long it takes to open the new spreadsheet once it has been created. I'm going to try and set the
xlApp.Visible property to False to see if that makes a difference. I will post any findings.

Thanks for all of your assistance!

Scott

 

[xlbo]

No probs - I have to say that I have never had that problem (long wait for excel to open) so I'm afraid I won't be much use in resolving that one. Glad you seem to have the rest of the code working though

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question