Office中国论坛/Access中国论坛

标题: 至少一个参数没有被指定 [打印本页]

作者: xiaoan614 时间: 2007-7-13 06:34
标题: 至少一个参数没有被指定
<

>我在access中用VBA代码写导入到excle的问题时，总提示“至少一个参数没有被指定”，查询过很多资料，有的说时不能在查询中设置查询参数，只能直接赋值，具体代码如下，请指点：
<

> ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

rivate Sub 导出到Excel_Click()
On Error GoTo Err_OutputToExcel Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlsheet As New Excel.Worksheet Dim Conn As New ADODB.Connection Dim Rec As New ADODB.Recordset Dim strSQL As String Dim i As Integer, j As Integer, m As Integer, n As Integer Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.add Set xlsheet = xlBook.Worksheets(1) Set Conn = CurrentProject.Connection
strSQL = "SELECT * FROM QUOTE_MAIN " strSQL = strSQL & "Where QUOTE_NO_TIMES =" & Me.QUOTE_NO_TIMES & ";" 
Rec.Open strSQL, Conn, adOpenStatic, adLockOptimistic
i = 1: j = 9 xlsheet.Name = Me.QUOTE_NO xlApp.Visible = True '显示
 With xlsheet .Columns("a:j").Font.Size = 10 .Columns("a:j").VerticalAlignment = xlVAlignCenter '垂直居中 .Columns("A:J").HorizontalAlignment = xlHAlignLeft '1列水平居中对齐 End With
 With xlsheet '设置列宽 .Cells(1, 1).ColumnWidth = 13 .Cells(1, 2).ColumnWidth = 20 .Cells(1, 3).ColumnWidth = 6 .Cells(1, 4).ColumnWidth = 7.5 .Cells(1, 5).ColumnWidth = 15 .Cells(1, 6).ColumnWidth = 10 .Cells(1, 7).ColumnWidth = 6 .Cells(1, 8).ColumnWidth = 15 .Cells(1, 9).ColumnWidth = 9 .Cells(1, 10).ColumnWidth = 15 End With '设置表头 xlApp.Range("A1:" & Chr(64 + Rec.Fields.Count) & 1).Select With xlApp.Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With xlApp.Selection.Merge xlApp.Range("A1:" & Chr(64 + Rec.Fields.Count) & 1).Select With xlApp.Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With xlApp.Selection.Merge xlApp.Range("A1").Select xlApp.ActiveCell.FormulaR1C1 = "QUOTE SHEET" With xlApp.Selection.Font .Name = "Arial Black" .Size = 16 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With
 xlApp.Range("A3").Select xlApp.ActiveCell.FormulaR1C1 = "PRICE NO." xlApp.Range("B3").Select xlApp.ActiveCell.FormulaR1C1 = QUOTE_NO xlApp.Range("A4").Select xlApp.ActiveCell.FormulaR1C1 = "PRICE TIMES" xlApp.Range("B4").Select xlApp.ActiveCell.FormulaR1C1 = QUOTE_TIMES xlApp.Range("A5").Select xlApp.ActiveCell.FormulaR1C1 = "FOLLOW NO." xlApp.Range("B5").Select xlApp.ActiveCell.FormulaR1C1 = SALPME_FOLLOW_NO xlApp.Range("A6").Select xlApp.ActiveCell.FormulaR1C1 = "FACTORY" xlApp.Range("B6").Select xlApp.ActiveCell.FormulaR1C1 = FACTORY xlApp.Range("A7").Select xlApp.ActiveCell.FormulaR1C1 = "CUSTOMER" xlApp.Range("B7").Select xlApp.ActiveCell.FormulaR1C1 = CUSTOMER xlApp.Range("H3").Select xlApp.ActiveCe

作者: wang1950317 时间: 2007-7-13 16:53
<

>常常碰到这样的提示，太菜弄不懂，有时就无目的的查勘表和查询，总会发现和代码有关联的表或查询有小错。改正就好了。如果更改表中某一列的列标，更会出现这种情况，如果在某些特定的列中误输入反常的字符，也会出现。还待请有经验的老师们指点。

作者: sgrshh29 时间: 2007-7-13 19:22
<DIV class=quote>以下是引用wang1950317在2007-7-13 8:53:00的发言： 
<

>常常碰到这样的提示，太菜弄不懂，有时就无目的的查勘表和查询，总会发现和代码有关联的表或查询有小错。改正就好了。如果更改表中某一列的列标，更会出现这种情况，如果在某些特定的列中误输入反常的字符，也会出现。还待请有经验的老师们指点。</DIV>
对于这种格式固定的excel，我从来不用这种方法，一般是做个excel模板，只要把access中的数据粘贴过去，然后另存就可以了。既简单，又灵活，还可避免出错，代码也少。

作者: zhengjialon 时间: 2007-7-13 19:51
看看查询中有没有参数:QUOTE_MAIN ,如果有是不行的.

作者: xiaoan614 时间: 2007-7-14 05:54
查询中没有QUOTE_MAIN啊，QUOTE_MAIN只是一个表

作者: 老鬼 时间: 2007-7-14 18:51
或许是QUOTE_NO_TIMES 数据类型的问题。

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)