I'm looking for help with copying a recordset to Excel. I created and tested
a query then copied and formatted the sql to code. When the spreadsheet opens,
there are no pasted records. I receive a code error, "command text was not
set for the command object". Here's the code I'm using:
Private Sub cmdExport_Click()
'Create a recordset named MyRecordset.
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn
'Build the SQL statement
Dim MySQL As String
MySQL = "SELECT Representatives.CID, "
MySQL = MySQL & " Representatives.NIC, "
MySQL = MySQL & " Representatives.[Last Name], "
MySQL = MySQL & " Representatives.[First Name], "
MySQL = MySQL & " [First Name] & ' ' & [Last Name] AS "
MySQL = MySQL & " [Full Name], Positions.Position, "
MySQL = MySQL & " Representatives.[Home Location Code], "
MySQL = MySQL & " [Location Assignments]."
MySQL = MySQL & " [Location Code] AS "
MySQL = MySQL & " [Assigned Location Code], "
MySQL = MySQL & " Locations.[Location Name], "
MySQL = MySQL & " Locations.[Local Union Number], "
MySQL = MySQL & " Representatives.Email, "
MySQL = MySQL & " Representatives.[Mobile Line], "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Outside Line], "
MySQL = MySQL & " [Location Phone Numbers].[Tie Line]"
MySQL = MySQL & " FROM Status INNER JOIN ((Positions "
MySQL = MySQL & " INNER JOIN Representatives ON "
MySQL = MySQL & " Positions.[Position Code] = "
MySQL = MySQL & " Representatives.[Position Code]) "
MySQL = MySQL & " INNER JOIN ((Locations INNER JOIN "
MySQL = MySQL & " [Location Assignments] ON "
MySQL = MySQL & " Locations.[Location Code] = "
MySQL = MySQL & " [Location Assignments]."
MySQL = MySQL & " [Location Code]) INNER JOIN "
MySQL = MySQL & " [Location Phone Numbers] ON "
MySQL = MySQL & " Locations.[Location Code] = "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Location Code]) ON "
MySQL = MySQL & " ([Location Assignments].CID = "
MySQL = MySQL & " Representatives.CID) AND "
MySQL = MySQL & " (Positions.[Position Code] = "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Position Code])) ON Status."
MySQL = MySQL & " [Status Code] = "
MySQL = MySQL & " Representatives.[Status Code]"
MySQL = MySQL & " WHERE (((Representatives."
MySQL = MySQL & " [Status Code])='1') AND "
MySQL = MySQL & " ((Locations.[Active Location])=Yes))"
MySQL = MySQL & " ORDER BY Representatives.[Last Name], "
MySQL = MySQL & " Representatives.[First Name];"
'Export the recordset to Excel
Dim MySheetPath As String
MySheetPath = "C:\Mike\AccessExport\ListofRepresentatives.xls"
'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(1).Visible = True
'Define th etopmost sheet in the Workbook as XLSheet.
Set XlSheet = XlBook.Worksheets(1)
'Copy the recordset to worksheet starting at cell A2
XlSheet.Range("A2").CopyFromRecordset MyRecordset
'Cleanup and end with worksheet visible on the screen.
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201004/1
|
|
0
|
|
|
|
Reply
|
 T5925MS
|
4/26/2010 3:20:56 PM |
|
I found the problem...First, I forgot to include MyRecordset.Open MySQL, and
second, I have a field named Position, which is a reserved word and therefore
needs to be enclosed in brackets, e.g [Position].
T5925MS wrote:
>I'm looking for help with copying a recordset to Excel. I created and tested
>a query then copied and formatted the sql to code. When the spreadsheet opens,
>there are no pasted records. I receive a code error, "command text was not
>set for the command object". Here's the code I'm using:
>
>Private Sub cmdExport_Click()
>
> 'Create a recordset named MyRecordset.
> Dim cnn As ADODB.Connection
> Set cnn = CurrentProject.Connection
> Dim MyRecordset As New ADODB.Recordset
> MyRecordset.ActiveConnection = cnn
>
> 'Build the SQL statement
> Dim MySQL As String
> MySQL = "SELECT Representatives.CID, "
> MySQL = MySQL & " Representatives.NIC, "
> MySQL = MySQL & " Representatives.[Last Name], "
> MySQL = MySQL & " Representatives.[First Name], "
> MySQL = MySQL & " [First Name] & ' ' & [Last Name] AS "
> MySQL = MySQL & " [Full Name], Positions.Position, "
> MySQL = MySQL & " Representatives.[Home Location Code], "
> MySQL = MySQL & " [Location Assignments]."
> MySQL = MySQL & " [Location Code] AS "
> MySQL = MySQL & " [Assigned Location Code], "
> MySQL = MySQL & " Locations.[Location Name], "
> MySQL = MySQL & " Locations.[Local Union Number], "
> MySQL = MySQL & " Representatives.Email, "
> MySQL = MySQL & " Representatives.[Mobile Line], "
> MySQL = MySQL & " [Location Phone Numbers]."
> MySQL = MySQL & " [Outside Line], "
> MySQL = MySQL & " [Location Phone Numbers].[Tie Line]"
> MySQL = MySQL & " FROM Status INNER JOIN ((Positions "
> MySQL = MySQL & " INNER JOIN Representatives ON "
> MySQL = MySQL & " Positions.[Position Code] = "
> MySQL = MySQL & " Representatives.[Position Code]) "
> MySQL = MySQL & " INNER JOIN ((Locations INNER JOIN "
> MySQL = MySQL & " [Location Assignments] ON "
> MySQL = MySQL & " Locations.[Location Code] = "
> MySQL = MySQL & " [Location Assignments]."
> MySQL = MySQL & " [Location Code]) INNER JOIN "
> MySQL = MySQL & " [Location Phone Numbers] ON "
> MySQL = MySQL & " Locations.[Location Code] = "
> MySQL = MySQL & " [Location Phone Numbers]."
> MySQL = MySQL & " [Location Code]) ON "
> MySQL = MySQL & " ([Location Assignments].CID = "
> MySQL = MySQL & " Representatives.CID) AND "
> MySQL = MySQL & " (Positions.[Position Code] = "
> MySQL = MySQL & " [Location Phone Numbers]."
> MySQL = MySQL & " [Position Code])) ON Status."
> MySQL = MySQL & " [Status Code] = "
> MySQL = MySQL & " Representatives.[Status Code]"
> MySQL = MySQL & " WHERE (((Representatives."
> MySQL = MySQL & " [Status Code])='1') AND "
> MySQL = MySQL & " ((Locations.[Active Location])=Yes))"
> MySQL = MySQL & " ORDER BY Representatives.[Last Name], "
> MySQL = MySQL & " Representatives.[First Name];"
>
> 'Export the recordset to Excel
> Dim MySheetPath As String
> MySheetPath = "C:\Mike\AccessExport\ListofRepresentatives.xls"
>
> 'Set up object variables to refer to Excel and objects.
> Dim Xl As Excel.Application
> Dim XlBook As Excel.Workbook
> Dim XlSheet As Excel.Worksheet
>
> 'Open an instance of Excel, open the workbook.
> Set Xl = CreateObject("Excel.Application")
> Set XlBook = GetObject(MySheetPath)
>
> 'Make sure everything is visible on the screen.
> Xl.Visible = True
> XlBook.Windows(1).Visible = True
>
> 'Define th etopmost sheet in the Workbook as XLSheet.
> Set XlSheet = XlBook.Worksheets(1)
>
> 'Copy the recordset to worksheet starting at cell A2
> XlSheet.Range("A2").CopyFromRecordset MyRecordset
>
> 'Cleanup and end with worksheet visible on the screen.
> MyRecordset.Close
> Set cnn = Nothing
> Set Xl = Nothing
> Set XlBook = Nothing
> Set XlSheet = Nothing
>
>End Sub
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201004/1
|
|
|
0
|
|
|
|
Reply
|
T5925MS
|
4/26/2010 5:51:38 PM
|
|
|
1 Replies
268 Views
(page loaded in 0.056 seconds)
Similiar Articles: Empty Recordset - microsoft.public.access.modulesdaovba ...I'm looking for help with copying a recordset to Excel. I created and tested a query then copied and formatted the sql to code. When the spreadsheet ... If query/subform is empty set calculation value to 0 - microsoft ...Actually, it probably won't do it, Jeff, since the query will return an empty recordset. Nz only works on Null values, not on values that don't exist. When is subform empty? - microsoft.public.accessIs there an event in the main form where I can find out if the subform is empty? ... You can use code in the main form's current event to check the subform's recordset. Problems with Error 3021 - No Current Record - microsoft.public.vb ...> > /Henning I am not sure about what situation you are asking, but one has to check for BOF and EOF before using MoveFirst in case the recordset is empty. No crrent record with empty subform - microsoft.public.access ...There has got to be something odd about the code that is dependent on the subform having a non empty recordset. > >>>Hm, okay, I didn't know that. Access 2007: bug? quick filter list is sometimes empty - microsoft ...However, sometimes the list of values simply is EMPTY in some columns. It ... Empty Recordset - microsoft.public.access.modulesdaovba ... Access 2007: bug? quick filter ... Show tabs with data in Tab Control - microsoft.public.access.forms ...If you need the captions to update when data >> is added to a subform which previously had an empty recordset, then I'd >> suggest using code in the AfterInsert event ... Help Wanted with phone numbers at bottom - microsoft.public.word ...Empty Recordset - microsoft.public.access.modulesdaovba ... I'm looking for help with copying a recordset to Excel. ... Outside Line], " > MySQL = MySQL & " [Location ... subform to display/gather attachments to records - microsoft ...Empty Recordset - microsoft.public.access.modulesdaovba ... Show tabs with data in Tab Control - microsoft ... How to determine if subform has record or not - microsoft ... open continuous form - shows 1st record not last.. - microsoft ...How can I set continuous form to open showing top of recordset, rather than bottom? Switchboard manager opens to show empty new Add row, even when se... Determining if a Recordset is Empty - 4GuysFromRolla.comIn Active Server Pages, once you run a query, you often want to be able to tell if a record set is empty or not. If the recordset is empty, you want to display a ... Testing for an Empty Recordset - Microsoft Access / VBATesting for an Empty Recordset. Get Microsoft Access / VBA help and support on Bytes. 10+ mistakes to avoid when using VBA Recordset objects | TechRepublicIn tip #3, I told you to use the BOF and EOF properties to test for an empty Recordset. You’ll also use them individually when moving through records. Visual Basic :: Detect Empty Recordset - BigResource: Webmaster ...Detect Empty Recordset Just a quickie, I've been using the below code to check for an empty recordset. Just wondering if this is the best/most efficient method for ... Empty Recordset? - VBForumsVisual Basic > Database Development ... Code: Private Sub Text3_Change() Set cn = New ADODB.Connection cn.ConnectionString ... you should put the cn.close on the end ... 7/24/2012 5:41:09 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
5 followers. 