CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
sConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};" ??
& "DBQ=" & arr_sPath(0)
Set wrk = CreateWorkspace("", "", "", dbUseODBC)
Set cnn = wrk.OpenConnection("", , , sConn)
Set rs = cnn.OpenRecordset("SELECT * FROM Customers", dbOpenDynamic)
iFieldCount = rs.Fields.Count
For i = 1 To iFieldCount
xlSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
CHAPTER 2 n DATA IN, DATA OUT 62
xlSheet.Range(xlSheet.Cells(1, 1), ??
xlSheet.Cells(1, rs.Fields.Count)).Font.Bold = True
xlSheet.Cells(2, 1).CopyFromRecordset rs
xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
'close workspace
wrk.Close
'release objects
Set xlSheet = Nothing
Set rs = Nothing
Set wrk = Nothing
Set cnn = Nothing
End Sub
Let??™s run this code from Sheet1 and see what it does.
1. Choose the Macros command from the Developer ribbon.
2. Select the DAOAccess2007ODBC macro from the list, and click Run. This should
generate an error, as shown in Figure 2-17.
Figure 2-17. DAO ODBC runtime error
3. Click the Debug button, and let??™s see where the code is stopping (see Figure 2-18).
Figure 2-18. CopyFromRecordset stops the code.
CHAPTER 2 n DATA IN, DATA OUT 63
Now we run into the error that I mentioned in the previous example. Excel??™s
CopyFromRecordsetmethod doesn??™t like the data type of a field or fields that we??™re returning in
the recordset being passed to it.
Pages:
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89