|
底下的指令雖然可執行讀取多個EXCEL檔案,但是into的資料表只會有最後一次讀取的檔案資料
- Private Sub t001_Click()
- 'Dim TbaName As String, TbbName As String '表名稱變數
- ' CurrentDb.Execute ("delete * from Main")
- If MsgBox("請準備好導入的文件!", vbOKCancel, "打印確認") = 1 Then
- Dim rs As Recordset
- Dim FN As String
- Dim URL() As Variant
- Dim i As Long
- ' On Error GoTo ER
- URL = dbFunction.Inport_From_Excel
- i = 1
- DoCmd.SetWarnings False
- 條件 = Me.週期
- Do Until i = UBound(URL) + 1
- 101 FN = Dir(URL(i))
-
- mySQL = "SELECT 特殊_1,備註_3,異常_3,代號_3" _
- & " INTO MAIN FROM [Excel 8.0;Database=" & URL(i) & "].[派單$] WHERE 周期 ='" & 條件 & "';" & " "
- DoCmd.RunSQL mySQL '運行SQL
- i = i + 1
- Loop
- MsgBox "您於" & Now() & "更新數據成功!", vbInformation '人性化提示
- DoCmd.Close acForm, Me.Name
- End If
-
- End Sub
复制代码 自訂函數
- Public Function Inport_From_Excel() As Variant
- Dim FileUrl As String
- Dim fso As Office.FileDialog
- Dim V() As Variant, c As Long, R As Long
- On Error GoTo exf
- Set fso = Application.FileDialog(msoFileDialogFilePicker)
- With fso
- .AllowMultiSelect = True
- .Filters.Clear
- .Filters.Add "xlsm", "*.xlsm"
- .Filters.Add "xlsx", "*.xlsx"
- .Filters.Add "xltx", "*.xltx"
- .Filters.Add "xltm", "*.xltm"
- .Filters.Add "xlsb", "*.xlam"
- .Filters.Add "ALL", "*.*"
- .Show
- End With
- R = fso.SelectedItems.Count
- ReDim V(1 To R)
- For c = 1 To R
- V(c) = fso.SelectedItems.Item(c)
- Next c
- Inport_From_Excel = V
- Exit Function
- exf:
- 'MsgBox VBA.Err.Description
- End Function
- Function fExistTable(strTableName As String)
- Dim db As dao.Database
- Dim i As Integer
- Set db = DBEngine.Workspaces(0).Databases(0)
- fExistTable = False
- db.TableDefs.Refresh
- For i = 0 To db.TableDefs.Count - 1
- If strTableName = db.TableDefs(i).Name Then
- 'Table Exists
- DoCmd.DeleteObject acTable, strTableName
- Exit For
- End If
- Next i
- Set db = Nothing
- End Function
复制代码
|
|