xl.Visible = True
xl.Workbooks.Open "C:\Documents and Settings\e531825\Desktop\positions_tmp.xls"
With xl
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).Name
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, "C:\Documents and Settings\e531825\Desktop\positions_tmp.xls"
Next i
End With
End With
Set xl = Nothing作者: todaynew 时间: 2012-12-11 11:53 本帖最后由 todaynew 于 2012-12-11 16:49 编辑
第三个参数为数据表名称吧?
试试这样搞法:
function 导入E(filename as string,tbname as string,sheetcnt as long)
'参数:filename--Excel文件(带路径)
' tbname--数据表名
' sheetcnt--标签总数(标签名为Sheet开头)
dim ssql as string
dim i as long
for i=1 to sheetcnt
ssql="insert into " & tbname & " select * from [Excel 8.0;DATABASE=" & filename & "].[Sheet" & i & "$]"
CurrentDb.Execute ssql
next
end function作者: access_beginner 时间: 2012-12-11 16:04
这样也可以:
Dim i As Long
Dim R As String
For i = 1 To 3
R = "Sheet" & i & "$"
DoCmd.TransferSpreadsheet acImport, , "表1", Me.Application.CurrentProject.Path & "\表.xls", True, R
Next
Me.List0.Requery
呵呵,榆木脑袋!{:soso_e120:}5楼的答复不是告诉你了吗?
TransferSpreadsheet方法的第5个参数为导入范围,该参数为一个字符串。如果是整个sheet导入的话,可以将该参数写为页的名称,并在其后加一个$符号。比如"sheet2$"、"1月份表$"诸如此类均可。所以你的代码可修改为:
Dim i As Long
Dim R As String
Dim tbname as string
For i = 1 To 3
R = "Sheet" & i & "$"
tbname="table" & i
DoCmd.TransferSpreadsheet acImport, , tbname, CurrentProject.Path & "\positions_tmp.xls", True, R
Next
版主,版主在吗?
我又遇到一个问题啊。用上面的方法有时候会报错误呃。
DoCmd.TransferSpreadsheet acImport, , "table", myFile, True, "Sheet3$"
错误内容为:
'Sheet3$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
而且经过研究表明,错误发生时都是在已经有excel文件打开的时候。如果有任何一个excel文件打开,那么跑这句话去导入code里面规定的文件就会报错。有什么办法可以解决的呢?
感谢版主指教!作者: access_beginner 时间: 2012-12-17 15:13