mani_singh
Well-known Member
- Joined
- Jul 24, 2007
- Messages
- 583
ok guys i have a problem, ive input this vba code and when i run it, i get a "run time error 13 - type mismatch error" im running excel 97 and have setup the correct microsoft reference libraries!
the location for the .mdb and the table name is fine - i just want it to go to the test.mdb file and copy across the data from it into sheet one of the spreadsheet im in.
Sub GetTable()
' variables
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path As String
Set Ws = Sheets("Sheet1")
Path = "h:\test.mdb"
Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
'Set the Database, and RecordSet This Table exists in the database
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
'This will set the RecordSet to all records in the Customers table
Set Rs = Db.OpenRecordset("kstable")
'This loop will collect the field names and place them in the first
'row starting at "A1"
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next i
'The next line simply formats the headers to bold font
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True
Ws.Range("A2").CopyFromRecordset Rs
Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
Rs.Close
Db.Close
End Sub
help please im kinda stuck here
the location for the .mdb and the table name is fine - i just want it to go to the test.mdb file and copy across the data from it into sheet one of the spreadsheet im in.
Sub GetTable()
' variables
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path As String
Set Ws = Sheets("Sheet1")
Path = "h:\test.mdb"
Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
'Set the Database, and RecordSet This Table exists in the database
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
'This will set the RecordSet to all records in the Customers table
Set Rs = Db.OpenRecordset("kstable")
'This loop will collect the field names and place them in the first
'row starting at "A1"
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next i
'The next line simply formats the headers to bold font
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True
Ws.Range("A2").CopyFromRecordset Rs
Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
Rs.Close
Db.Close
End Sub
help please im kinda stuck here