Problem on Exporting 2003Access parameters Query to 2003Excel

**Jim Doherty** · Dec 15 '07, 02:51 AM

Originally posted by QCLee

Sir can you help me to transfer my Access Query to MS excel? i have a
command button on the form to export the parameter query named
"HVACWindwardQu ery" to excel spreadsheet and i got the codes from
searching on the internet and books but the problem is when i run the
command button "Export" it just only open the Blank Spreadsheet, no
data at all that it came from my query named "HVACWindwardQu ery" and
there's an error on it "Run-time error '3061': Too few parameters.
Expected 2." and when i open the VBA editor this is an error that is on
highlight in yellow:
Set objRST = Application.Cur rentDb.OpenReco rdset(strQueryN ame) ....can
you help me to solve this problem, here is the code that im using it
came from the book but its not working out:

Code:

 Private Sub cmdExport_Click() 
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
strQueryName = "HVACWindwardQuery"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
 
Set xlSheet = xlWorkbook.Sheets(1)
For lvlColumn = 0 To objRST.Fields.Count - 1
xlSheet.Cells(1, lvlColumn + 1).Value = _
objRST.Fields(lvlColumn).Name
Next
'Change the font to bold for the header row
xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
'Add a border to header row cells
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlSheet
.Range("A2").CopyFromRecordset objRST
.Name = Left(strQueryName, 31)
End With
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
End Sub

and this is the SQL View:

Code:

 
SELECT [HVAC Windward Table].[EQUIPMENT / PM NUMBER], [HVAC Windward Table].[TAG NO#], [HVAC Windward Table].[FACILITY NUMBER], [HVAC Windward Table].[BUILDING / LOCATION], [HVAC Windward Table].[EQUIPMENT TYPE], [HVAC Windward Table].[FREQ PM], [HVAC Windward Table].DATE, [HVAC Windward Table].REMARKS, [HVAC Windward Table].[JOB PLAN], [HVAC Windward Table].[FILTER SIZE], [HVAC Windward Table].[WORK ORDER NO], [HVAC Windward Table].[REPORTED DATE], [HVAC Windward Table].[TAG NO], [HVAC Windward Table].[ANNEX/SUB-ANNEX], [HVAC Windward Table].[SPEC NO], [HVAC Windward Table].[JOB DESCRIPTION], [HVAC Windward Table].RATING, [HVAC Windward Table].[COMMENTS/ACTION], [HVAC Windward Table].[INSPECTOR NAME]
FROM [HVAC Windward Table]WHERE ((([HVAC Windward Table].DATE)>=forms![HVAC Windward Form]!txtdatefrom And ([HVAC Windward Table].DATE)<=forms![HVAC Windward Form]!txtDateTo));

Still searchin to solve this problem and i read it on the book it says:
The query requires parameters, it should be probably use an alternate
method of opening a query and that is an OpenRecordset method. I don't
know how to use or create a code for OpenRecordset pls try to give me a
code using OpenRecordset method of the QueryDef object to open a
parameter query and Transport my query named "HVACWindwardQu ery" into
Excel Spreadsheet or the best method code for this problem. In my Form
there is two(2) date to fill in and once you click the button it will
go to "HVACWindwardQu ery" and preview my Report and i created a button
again to Export the query to Excel but is not working at all it gives
me a blank spreadsheet. Pls help me... Thanks and god bless..

Hi QC,

Welcome to the scripts!

I edited your post to include code tags around your posted code. Please remember to do that if you post back (your first post accepted) :)

Ok in relation to your posted code I have amended it to reference the querydef and open a snapshot recordset. Included in that is a reference to your form controls that supply the parameters for the query.

The changes made I have highlighted in bold and at the end of each line added an XXX so you can study the differences between the two
I have commented out those line within it that are not needed in view of amendments made.

The below should now work for you

Code:

Private Sub cmdExport_Click()
	Dim xlApp As Excel.Application
	Dim xlSheet As Excel.Worksheet
	Dim xlWorkbook As Excel.Workbook
	Dim acQuery As QueryDef
	Dim objRST As Recordset
	[b]'Dim strQueryName As String 'XXX not now needed[/b]
[b]'strQueryName = "HVACWindwardQuery" 'XXX not now needed[/b]
	Set xlApp = CreateObject("Excel.Application")
	xlApp.Visible = True
	Set xlWorkbook = xlApp.Workbooks.Add
 
	[b]Set acQuery = CurrentDb.QueryDefs("HVACWindwardQuery") 'XXX[/b]
 
[b]	acQuery![forms![HVAC Windward Form]!txtdatefrom] = Me!txtDateFrom 'XXX[/b]
[b]	acQuery![forms![HVAC Windward Form]!txtdateTo] = Me!txtDateTo 'XXX[/b]
[b]'	Set objRST = Application.CurrentDb.OpenRecordset(strQueryName) 'XXX not now needed[/b]
 
	[b]Set objRST = acQuery.OpenRecordset(dbOpenSnapshot) 'XXXX[/b]
 
	Set xlSheet = xlWorkbook.Sheets(1)
	For lvlColumn = 0 To objRST.Fields.Count - 1
		xlSheet.Cells(1, lvlColumn + 1).Value = _
		objRST.Fields(lvlColumn).Name
	Next
	'Change the font to bold for the header row
	xlSheet.Range(xlSheet.Cells(1, 1), _
				 xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
	'Add a border to header row cells
	With xlSheet.Range(xlSheet.Cells(1, 1), _
					 xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeLeft)
		.LineStyle = xlContinuous
		.Weight = xlThin
		.ColorIndex = xlAutomatic
	End With
	With xlSheet.Range(xlSheet.Cells(1, 1), _
					 xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeTop)
		.LineStyle = xlContinuous
		.Weight = xlThin
		.ColorIndex = xlAutomatic
	End With
	With xlSheet.Range(xlSheet.Cells(1, 1), _
					 xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeBottom)
		.LineStyle = xlContinuous
		.Weight = xlThin
		.ColorIndex = xlAutomatic
	End With
	With xlSheet.Range(xlSheet.Cells(1, 1), _
					 xlSheet.Cells(1, objRST.Fields.Count)).Borders(xlEdgeRight)
		.LineStyle = xlContinuous
		.Weight = xlThin
		.ColorIndex = xlAutomatic
	End With
	With xlSheet
		.Range("A2").CopyFromRecordset objRST
		[b]'.Name = left(strQueryName, 31) 'XXX not now needed[/b]
	 [b].Name = left(acQuery.Name, 31) 'XXX[/b]
	End With
	Set xlSheet = Nothing
	Set xlWorkbook = Nothing
	Set xlApp = Nothing
	[b]objRST.Close  'XXX[/b]
	[b]Set objRST = Nothing  'XXX[/b]
[b]	Set acQuery= Nothing  'XXX[/b]
 
End Sub

Regards

Jim:)

**QCLee** · Dec 15 '07, 07:03 PM

Sir Jim,

I'm sorry for not having a code tags in my posted code, next time i will do that. Well, how can i thank you? The code is now perfectly working in my command button. Thank's for helping me, your such a great person..This code will be useful to others in solving parameter query to excel . Thanks and god bless!

**Jim Doherty** · Dec 15 '07, 11:40 PM

Originally posted by QCLee

Sir Jim,

I'm sorry for not having a code tags in my posted code, next time i will do that. Well, how can i thank you? The code is now perfectly working in my command button. Thank's for helping me, your such a great person..This code will be useful to others in solving parameter query to excel . Thanks and god bless!

Hi QC

You are very welcome and thanks for posting back. You have no need to apologise I wasnt chastising :)) it is your first post you are not necessarily going to know first time..... in I know I didnt!!

Anyway I,m pleased it worked for you.Have a good festive season

Regards

Jim :)

**takashi2009** · Dec 16 '07, 07:38 PM

Hi Jim,
I also have a question that is related to this task. I am attempting to put together some VBA to help in automating some heavily manually intensive Access and Excel tasks done by about five people at my workplace. I do not have a background in VBA, but have done a fair amount of reading on it over the past week and pieces are coming together slowly. My question is; What would one way be to modify the above code so that if the query was run again, but with a different input, the query would export to a second worksheet instead of overwriting the first. Thank s in advance. The users of this at work will be so happy once it's up and running.

**Jim Doherty** · Dec 16 '07, 07:53 PM

Originally posted by takashi2009

Hi Jim,
I also have a question that is related to this task. I am attempting to put together some VBA to help in automating some heavily manually intensive Access and Excel tasks done by about five people at my workplace. I do not have a background in VBA, but have done a fair amount of reading on it over the past week and pieces are coming together slowly. My question is; What would one way be to modify the above code so that if the query was run again, but with a different input, the query would export to a second worksheet instead of overwriting the first. Thank s in advance. The users of this at work will be so happy once it's up and running.

Hi Tak,

Please explain precisely what you mean by different input. I know I can guess that you might mean the name of a different query (maybe five different queries all named differently, conversely it could mean one query of a particular name but having different parameters on which it performs its function within itself)

As you can imagine 'logic' is somewhat specific to circumstance and one needs to know 'precisely' what it is you need a routine to do

Regards

Jim :)

**takashi2009** · Dec 17 '07, 12:11 AM

Originally posted by Jim Doherty

Hi Tak,

Please explain precisely what you mean by different input. I know I can guess that you might mean the name of a different query (maybe five different queries all named differently, conversely it could mean one query of a particular name but having different parameters on which it performs its function within itself)

As you can imagine 'logic' is somewhat specific to circumstance and one needs to know 'precisely' what it is you need a routine to do

Regards

Jim :)

Hi Jim,
I really appreciate your quick response. I have envisioned the process flow such that a user inputs project titles to a form, which references the query, which then exports to Excel. This is for a reporting process which typically involves 10 or so identical queries run on different project titles.
Ideally what I want to happen is for a series of 10 or so queries with input boxes to be set up using a Macro on a single form which will sequentially output the query results to an Excel workbook into different sheets. The process in place now is as a query set up as a parameter query. The user has to input the project title and manually export the query results to excel for a series of 10 or so different project titles.

**Jim Doherty** · Dec 17 '07, 12:44 AM

Originally posted by takashi2009

Hi Jim,
I really appreciate your quick response. I have envisioned the process flow such that a user inputs project titles to a form, which references the query, which then exports to Excel. This is for a reporting process which typically involves 10 or so identical queries run on different project titles.
Ideally what I want to happen is for a series of 10 or so queries with input boxes to be set up using a Macro on a single form which will sequentially output the query results to an Excel workbook into different sheets. The process in place now is as a query set up as a parameter query. The user has to input the project title and manually export the query results to excel for a series of 10 or so different project titles.

Ok tak I get the idea...so that I can code something that represents your actual tablename and fieldnames and a typical query are you able to post the structure of the table and the syntax of a typical query that you currently are using. That way, anything I do will represent itelf properly on your system as opposed to you having to remap anything

An example of posting ..something like this

TableName
tblMyTable
FieldName1 datatype text
Fieldname2 datatype number
FieldName3 datatype memo

and so on
SQL systaxt derived from the SQL window

SELECT Fieldname1,Fiel dname2 FROM tblMyTable WHERE etc etc

Jim :)

**takashi2009** · Dec 17 '07, 02:32 AM

Originally posted by Jim Doherty

Ok tak I get the idea...so that I can code something that represents your actual tablename and fieldnames and a typical query are you able to post the structure of the table and the syntax of a typical query that you currently are using. That way, anything I do will represent itelf properly on your system as opposed to you having to remap anything

An example of posting ..something like this

TableName
tblMyTable
FieldName1 datatype text
Fieldname2 datatype number
FieldName3 datatype memo

and so on
SQL systaxt derived from the SQL window

SELECT Fieldname1,Fiel dname2 FROM tblMyTable WHERE etc etc

Jim :)

Hi again Jim, I didn't expect the level of help that you are providing, and I really do appreciate it. The analysts will be happy this week.

This is the query that is being run;

Code:

 
PARAMETERS [Enter the name of the project] Text ( 255 );
SELECT ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client SO ID Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client PO Num], [AOR - Grouped].Ship_To_ID, [AOR - Grouped].Ship_to_Desc AS Consignee, ShOrRpt_ShLvl_With_ItemsbyContainer.City, ShOrRpt_ShLvl_With_ItemsbyContainer.ST, ShOrRpt_ShLvl_With_ItemsbyContainer.Zip, ShOrRpt_ShLvl_With_ItemsbyContainer.jba_line_no AS Line_No, ShOrRpt_ShLvl_With_ItemsbyContainer.item_id, ShOrRpt_ShLvl_With_ItemsbyContainer.item_desc, ShOrRpt_ShLvl_With_ItemsbyContainer.qty AS Qty_Shipped, [Qty]*[unit_per_pack] AS Exploded_Units, ShOrRpt_ShLvl_With_ItemsbyContainer.[Tender Date] AS [Ship Date], [AOR - Grouped].CarrierName AS [Carrier Name], ShOrRpt_ShLvl_With_ItemsbyContainer.[Tracking Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Pro Num], [AOR - Grouped].Service_Level_Desc AS Service_Level, IIf([status]="Closed","Delivered",IIf([status]="Possible D","Possible Delay",IIf([status]="OPEN","Open",IIf([status]="EXCEPTION","Exception",[status])))) AS Status_, IIf([Delivery Date]<#1/1/2006#,Null,[Delivery Date]) AS POD_Date, IIf((IsNull([delivery Date]) Or [delivery date]<#1/1/2006#) And [status]="Open" And ([POD Time]=#12/30/1899#),Null,Format([POD Time],"hh:nn AM/PM")) AS POD_Time, ShOrRpt_ShLvl_With_ItemsbyContainer.[POD Name], ShOrRpt_ShLvl_With_ItemsbyContainer.Origin
FROM ShOrRpt_ShLvl_With_ItemsbyContainer INNER JOIN [AOR - Grouped] ON ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num] = [AOR - Grouped].gold_so_no
WHERE (((ShOrRpt_ShLvl_With_ItemsbyContainer.projectname)=[Enter the name of the project]));

The Form is titled Project Title along with the combo box. The Macro is entitled ExportToExcel. The table is entitled "ShOrRpt_ShLvl" .
What I am trying to do precisely is export the results of this query to the 6th row of each worksheet in the workbook that will be used. The first 5 rows will contain column headers and title information from a template. Each user traditionally has run the query approximately 10 times for each different project name title to produce 10 different reports. I really appreciate your help and have spent most of last week and this weekend reading up on VBA and Access.

**Jim Doherty** · Dec 17 '07, 03:39 AM

Originally posted by takashi2009

Hi again Jim, I didn't expect the level of help that you are providing, and I really do appreciate it. The analysts will be happy this week.

This is the query that is being run;

Code:

 
PARAMETERS [Enter the name of the project] Text ( 255 );
SELECT ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client SO ID Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Client PO Num], [AOR - Grouped].Ship_To_ID, [AOR - Grouped].Ship_to_Desc AS Consignee, ShOrRpt_ShLvl_With_ItemsbyContainer.City, ShOrRpt_ShLvl_With_ItemsbyContainer.ST, ShOrRpt_ShLvl_With_ItemsbyContainer.Zip, ShOrRpt_ShLvl_With_ItemsbyContainer.jba_line_no AS Line_No, ShOrRpt_ShLvl_With_ItemsbyContainer.item_id, ShOrRpt_ShLvl_With_ItemsbyContainer.item_desc, ShOrRpt_ShLvl_With_ItemsbyContainer.qty AS Qty_Shipped, [Qty]*[unit_per_pack] AS Exploded_Units, ShOrRpt_ShLvl_With_ItemsbyContainer.[Tender Date] AS [Ship Date], [AOR - Grouped].CarrierName AS [Carrier Name], ShOrRpt_ShLvl_With_ItemsbyContainer.[Tracking Num], ShOrRpt_ShLvl_With_ItemsbyContainer.[Pro Num], [AOR - Grouped].Service_Level_Desc AS Service_Level, IIf([status]="Closed","Delivered",IIf([status]="Possible D","Possible Delay",IIf([status]="OPEN","Open",IIf([status]="EXCEPTION","Exception",[status])))) AS Status_, IIf([Delivery Date]<#1/1/2006#,Null,[Delivery Date]) AS POD_Date, IIf((IsNull([delivery Date]) Or [delivery date]<#1/1/2006#) And [status]="Open" And ([POD Time]=#12/30/1899#),Null,Format([POD Time],"hh:nn AM/PM")) AS POD_Time, ShOrRpt_ShLvl_With_ItemsbyContainer.[POD Name], ShOrRpt_ShLvl_With_ItemsbyContainer.Origin
FROM ShOrRpt_ShLvl_With_ItemsbyContainer INNER JOIN [AOR - Grouped] ON ShOrRpt_ShLvl_With_ItemsbyContainer.[Gold Order Num] = [AOR - Grouped].gold_so_no
WHERE (((ShOrRpt_ShLvl_With_ItemsbyContainer.projectname)=[Enter the name of the project]));

The Form is titled Project Title along with the combo box. The Macro is entitled ExportToExcel. The table is entitled "ShOrRpt_ShLvl" .
What I am trying to do precisely is export the results of this query to the 6th row of each worksheet in the workbook that will be used. The first 5 rows will contain column headers and title information from a template. Each user traditionally has run the query approximately 10 times for each different project name title to produce 10 different reports. I really appreciate your help and have spent most of last week and this weekend reading up on VBA and Access.

Hi Tak,

!) Is the table called ShOrRpt_ShLvl
or
is it called
ShOrRpt_ShLvl_W ith_ItemsbyCont ainer
or
is ShOrRpt_ShLvl_W ith_ItemsbyCont ainer a query based on a table called
ShOrRpt_ShLvl

I am happy to help you on this... but the thread will be too long ping ponging back and forth with minor clarifications and so on with what I have in mind for doing with this. I am going to deal with it off line with you and I will concisely report back the findings to this thread eventually so that others may benefit from any solution.

If you private message me with your email address I will email you

Jim :)

Problem on Exporting 2003Access parameters Query to 2003Excel

Problem on Exporting 2003Access parameters Query to 2003Excel

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment