使用SQL\VBA获取运费最优方案

fankairong

一般企业涉及到运输的都必然会同时跟多家货运公司要报价单,而由于各个货运公司的优势不同,到同一地方的价格必然也会有高有低.那么我们应该如何用SQL与VBA的方式来获取最优价格呢?

1. Sub getbestprice()
   
2.     Dim conn As Object, sql As String, ton As Double, tcity As String
   
3.     Set conn = CreateObject("ADODB.Connection")
   
4.     tcity = [F2].Value
   
5.     conn.Open "Provider=microsoft.jet.oledb.4.0;Extended Properties='Excel 8.0';data source=" & ThisWorkbook.FullName
   
6.     sql = "select top 1 单价,货站 from [运价表$A:C] where 单价=(Select min(单价) From [运价表$A:C] where 城市='" & tcity & "')"
   
7.     [G2].CopyFromRecordset conn.Execute(sql)
   
8. End Sub

复制代码

运费报价.rar (13.54 KB, 下载次数: 43)

2012-7-22 08:37 上传

点击文件名下载附件

魂断蓝桥

麻烦看一下，这个语句为何自动运行错误，是什么原因？



Sub bbb()
Dim cnn As Object
  Dim rst As Object
  Dim Sql As String
  Dim tim As Long
  Date1 = [c1] * 1
   Set cnn = CreateObject("ADODB.CONNECTION")
  Set rst = CreateObject("ADODB.RECORDSET")
  
  cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
  Sql = "select 名称,公司名, " & "[" & Date1 & "] from [Sheet1$a:az]"

Range("j1:m65536").ClearContents
  '[j2].CopyFromRecordset cnn.Execute(Sql)
  cnn.Close
  Set cnn = Nothing

  [e1] = Sql
End Sub

订单表.rar (12.17 KB, 下载次数: 12)

2012-7-22 09:10 上传

点击文件名下载附件

xiaofx11

1. sql = "SELECT TOP 1 货站,城市,单价 from [运价表$] where 城市='" & tcity & "' order by 单价"

复制代码

fankairong

魂断蓝桥 发表于 2012-7-22 09:10
麻烦看一下，这个语句为何自动运行错误，是什么原因？

因为你的字段名不规则,所以不能直接用列标题取,而应该用顺序号取,更改HDR=No

1. Sub bbb()
   
2.     Dim cnn As Object
   
3.     Dim rst As Object
   
4.     Dim Sql As String
   
5.     Dim tim As Long
   
6.     date1 = "f" & Application.Match(Worksheets("sheet2").[c1], Worksheets("sheet1").Range("1:1"), 0)
   
7.     Set cnn = CreateObject("ADODB.CONNECTION")
   
8.     Set rst = CreateObject("ADODB.RECORDSET")
   
9. 
   
10.     cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0;HDR=no'; Data Source=" & ThisWorkbook.FullName
    
11.    
    
12.     Sql = "select f1,f2," & date1 & "  from [Sheet1$A:az]"
    
13.     Range("j1:m65536").ClearContents
    
14.     [j2].CopyFromRecordset cnn.Execute(Sql)
    
15.     cnn.Close
    
16.     Set cnn = Nothing
    
17. 
    
18.     [e1] = Sql
    
19. End Sub

复制代码