I have written the following function and can't get it to work.
I tried this procedure in a access project (as the tables reside on a SQL server)
but then I get an error message "Object variable or with block variable not set"
I then linked some of these tables in an ordinary access db and then get no error message,
but nothing happens (!)
At least in the project file I know that I get an expected table name (one that starts with 'STS_')
into obj.name in the set rs statement.
I also don't know how to ensure that I export all 4600 odd tables and that none are duplicated or excluded.
Can someone PLEASE help me? As you might have noticed I am approaching this problem from more than one angle - please feel free to point me in yet another even better direction.
Thanks!
Function ExportTables()
Dim obj As AccessObject, dbs As Object
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set dbs = Application.CurrentData
For Each obj In dbs.alltables
If obj.Name Like "STS_*" Then
Set rs = CurrentDb.OpenRecordset(obj.Name, dbOpenDynaset)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
intMaxCol = rs.Fields.Count
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If
End If
Next obj
End Function
I tried this procedure in a access project (as the tables reside on a SQL server)
but then I get an error message "Object variable or with block variable not set"
I then linked some of these tables in an ordinary access db and then get no error message,
but nothing happens (!)
At least in the project file I know that I get an expected table name (one that starts with 'STS_')
into obj.name in the set rs statement.
I also don't know how to ensure that I export all 4600 odd tables and that none are duplicated or excluded.
Can someone PLEASE help me? As you might have noticed I am approaching this problem from more than one angle - please feel free to point me in yet another even better direction.
Thanks!
Function ExportTables()
Dim obj As AccessObject, dbs As Object
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set dbs = Application.CurrentData
For Each obj In dbs.alltables
If obj.Name Like "STS_*" Then
Set rs = CurrentDb.OpenRecordset(obj.Name, dbOpenDynaset)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
intMaxCol = rs.Fields.Count
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If
End If
Next obj
End Function