|
本帖最后由 xiaovba 于 2019-8-15 11:10 编辑
主程序调用时是用的call 子程序(参数)
子程序:
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0;HDR=Yes;IMEX=1';data source=" & ThisWorkbook.FullName
ThisWorkbook.Sheets(w).Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ThisWorkbook.Sheets(w).Range("a5:h5").Copy ThisWorkbook.Sheets(w).Range("a" & i & ":h" & i) ‘制作标题行
ThisWorkbook.Sheets(w).Name = "sht" ’修改表名
r = ThisWorkbook.Sheets("sht").Range("h1048576").End(xlUp).Row
sql = "select a.* from [temp1$] a inner join (select distinct [Store Code] from [sht$a" & i & ":g" & r
sql = sql & "])b on a.STORE = b.[Store Code]"
Workbooks.Add
ActiveSheet.[a2].CopyFromRecordset cnn.Execute(sql) '这里报错,说无法识别'sht$a8:g116' ,程序里面i=8.r=116
我把这段单独copy到新的模块里是能运行的。我查了下主程序,在调用之前set cnn=nothing了的。
|
|