flowergirl
08-15-2011, 10:35 PM
This code retrieves data all on the leftmost corner of the worksheet. How can I modify data so that Employee_Name is in B2, Employee Salary is in B3 and the headers are not there. I could not attach the Access db because of security restrictions. It's structure is as:
Employee ID Employee_Name Employee Salary
3 Sam 800
4 Mary 500
5 Bill 600
Thanks for your help.
Sub AccessToExcel()
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet2")
dbFileName = "C:\Documents and Settings\ADRY_7258\My Documents\Test_Access_db.accdb"
dbConnection.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName _
& ";Persist Security Info=False;"
strSQL = "SELECT Test_Access_db.* from Test_Access_db where Employee_name = 'Bill'"
DestinationSheet.Cells.Clear
With dbConnection
.Open
.Cursorlocation = adUseClient
End With
With dbRecordset
.Open strSQL, dbConnection
Set .ActiveConnection = Nothing
End With
DestinationSheet.Range("A2").CopyFromRecordset dbRecordset
DestinationSheet.Range("A1:C1").Value = Array("Employee ID", "Employee Name", "Employee Salary")
dbRecordset.Close
dbConnection.Close
Set dbRecordset = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing
End Sub
Employee ID Employee_Name Employee Salary
3 Sam 800
4 Mary 500
5 Bill 600
Thanks for your help.
Sub AccessToExcel()
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet2")
dbFileName = "C:\Documents and Settings\ADRY_7258\My Documents\Test_Access_db.accdb"
dbConnection.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName _
& ";Persist Security Info=False;"
strSQL = "SELECT Test_Access_db.* from Test_Access_db where Employee_name = 'Bill'"
DestinationSheet.Cells.Clear
With dbConnection
.Open
.Cursorlocation = adUseClient
End With
With dbRecordset
.Open strSQL, dbConnection
Set .ActiveConnection = Nothing
End With
DestinationSheet.Range("A2").CopyFromRecordset dbRecordset
DestinationSheet.Range("A1:C1").Value = Array("Employee ID", "Employee Name", "Employee Salary")
dbRecordset.Close
dbConnection.Close
Set dbRecordset = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing
End Sub