至少一个参数没有被指定(页 1) - ACCESS

xiaoan614 发表于 2007-7-12 22:40

至少一个参数没有被指定

我在access中用VBA代码写导入到excle的问题时，总提示“至少一个参数没有被指定”，查询过很多资料，有的说时不能在查询中设置查询参数，只能直接赋值，具体代码如下，请指点：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ Private 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.ActiveCell.FormulaR1C1 = "ISSUE ORDER" xlApp.Range("I3").Select xlApp.ActiveCell.FormulaR1C1 = ISSUE_ORDER xlApp.Range("H4").Select xlApp.ActiveCell.FormulaR1C1 = "DATE" xlApp.Range("I4").Select xlApp.ActiveCell.FormulaR1C1 = Format(DATE, "YYYY-MM-DD") With xlsheet .Range("A3:I7").Font.Bold = True '标题字体加粗 End With xlApp.Rows("3:7").Select With xlApp.Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With '设置边框 xlApp.Range("A" & j & ":" & Chr(64 + Rec.Fields.Count) & Rec.RecordCount + j).Select xlApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone xlApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone With xlApp.Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With '自动换行 With xlApp.Selection .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext End With '设置字体 With xlApp.Selection.Font .Name = "Arial" .Size = 9 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With xlsheet '设置列标题 For m = 0 To Rec.Fields.Count - 1 .Cells(j, m + 1) = Rec.Fields(m).Name Next '设置表主体内容 Do While Not Rec.EOF For n = 0 To Rec.Fields.Count - 1 .Cells(i + j, n + 1) = Rec.Fields(n) Next i = i + 1 Rec.MoveNext Loop End With xlApp.Range(("A" & Rec.RecordCount + j + 2), ("J" & Rec.RecordCount + j + 2)).Select xlApp.ActiveCell.FormulaR1C1 = "NOTE：" xlApp.Selection.Merge '设置字体 With xlApp.Selection.Font .Name = "Arial" .Size = 9 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With xlApp.Range(("A" & Rec.RecordCount + j + 3), ("J" & Rec.RecordCount + j + 3)).Select xlApp.ActiveCell.FormulaR1C1 = NOTE xlApp.Selection.Merge '设置字体 With xlApp.Selection.Font .Name = "Arial" .Size = 9 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Rec.close Set Rec = Nothing Set Conn = Nothing Set xlApp = Nothing Set xlsheet = Nothing Exit_OutputToExcel: Exit Sub Err_OutputToExcel: Set Rec = Nothing Set Conn = Nothing Set xlApp = Nothing Set xlsheet = Nothing MsgBox Err.description Resume Exit_OutputToExcel End Sub ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ 望高人指点啊，我这个问题已经困扰我好几天了，呜呼……

bygg 发表于 2007-7-13 18:06

这么多,错误显示到哪一行的?>

xiaoan614 发表于 2007-7-13 21:51

我想应该是查询语句的问题，但是怎么改，问题在哪实在是弄不懂了，如果不加查询条件是可以的，但关键没查询条件查询出的数据非常多，而且不能满足需求啊。 
strSQL = "SELECT * FROM QUOTE_MAIN " strSQL = strSQL & "Where QUOTE_NO_TIMES =" & Me.QUOTE_NO_TIMES & ";"

zkqbp 发表于 2007-7-14 08:04

我用isnull()函数在查询语句中，程序运行时也出现【至少一个参数没有被指定】的提示，是否access的版本太低的原因吧？请问各位高手[em01]

页: [1]