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")
    xlObj.Workbooks.Add
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
    Next

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

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

Dim dblLastRow As Double

With Sheet.Range("A1").CurrentRegion
    .EntireColumn.AutoFit
    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

Comment
Watch Question

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

cheers
cslarsen

Author

Commented:
thanks,

I corrected this but still get the same error messages.

Commented:
Np, just thought it would fit the nature of the problem.
cheers
cslarsen
NorieAnalyst Assistant

Commented:
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).

Author

Commented:
imnorie:

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.

Author

Commented:
kooswillem:

Sorry don't understand what you are saying.

Author

Commented:
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.

Author

Commented:
Question closed as no answer and need to clean up.