We help IT Professionals succeed at work.

Create excel from access - issue formating table

Attached vba code generates an excel spreadsheet.

I am trying to format the spreadsheet as a table in Excel 2007. Looking to get alternating blue lines.

All works OK untill I run it a second time in the database - It either comes up with "Run time error 5 - invaild call or argument" if I have the first excel spreadsheet still open.

If I close excel before running again I get 'run time error '1004' method 'Range' of object _Global failed"

error on line "xlObj.ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1", strRangeCell), , xlYes).NAME = strTableName"

If I close access and open again works fine (first time).

Running this in Access 2007 in a adp database.

Appreciate any ideas on what could be causing this.
Sub test()

Dim con As Object
Set con = Application.CurrentProject.Connection

Dim rstExcel As ADODB.Recordset
Dim strSQLExcel As String
Dim i As Integer, j As Integer
Dim ssql As String, xlFile As String
Dim xlObj As Object
Dim Sheet As Object

strSQLExcel = "SELECT * from DR_Accs"

Set rstExcel = CreateObject("ADODB.Recordset")
rstExcel.Open strSQLExcel, con, 1   ' 1 = adOpenKeyset

Set xlObj = CreateObject("Excel.Application")
Set Sheet = xlObj.ActiveWorkbook.Sheets(1)
'copy the headers
Dim iRow, iCol
iRow = 1
    For iCol = 0 To rstExcel.Fields.Count - 1
        Sheet.Cells(iRow, iCol + 1).Value = rstExcel.Fields(iCol).NAME

Sheet.Range("A2").CopyFromRecordset rstExcel
xlObj.Visible = True

Sheet.Rows("1:1").Font.Bold = True

Dim dblLastRow As Double

With Sheet.Range("A1").CurrentRegion
    dblLastRow = .Rows.Count
End With

If CInt(xlObj.Version) = 12 Then
    Dim strRangeCell As String
    strRangeCell = Sheet.Cells(dblLastRow, rstExcel.Fields.Count).Address
    Dim strTableName As String
    strTableName = "Table" & Format(Now, "ddmmyyhmm")
    xlObj.ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1", strRangeCell), , xlYes).NAME = strTableName
    xlObj.Range(strTableName & "[#All]").Select
    xlObj.ActiveSheet.ListObjects(strTableName).TableStyle = "TableStyleMedium2"
End If

Set Sheet = Nothing

Set xlObj = Nothing
End Sub

Open in new window

Watch Question

I miss
Set rstExcel = Nothing
to release the recordset.....




I corrected this but still get the same error messages.

Np, just thought it would fit the nature of the problem.
NorieAnalyst Assistant

Are you using the table for anything else other than this formatting?

The reason I ask is because the formatting could easily be done without creating/involving tables.

Don't know if that would help but it would get rid of all the table stuff which seems to be what is causing the problem(s).



Yes only trying to format the grid with lines so if there is an easier way to do this I would be interested to know.

So follow these steps:

1. Create a new Tab. Copy / paste or refer to the selected columns
2. Apply advanced sorting on Date/time and position
3. For the last point you want to aggregate part the data sequentually on the same row.
I will get back to you on this.

For future reference in these cases; for this type of solution, it is easier to create a Access or more advanced database.



Sorry don't understand what you are saying.


Been told this question needs to be cleaned up - I will try and sort out some time but have spent quite some time on this already and no closer.


Question closed as no answer and need to clean up.