AW: Excel Dateien zusammenfügen mit einem Tool
07.02.2018 20:47:46
Simbu
Ich habe hier eine erste Variante:
Wie bekomme ich es nun hin, dass sich noch die zwei anderen Pfade ebenfalls öffnen und ich dann von allen 3 die Dateien auslesen kann?
Danke für eure Hilfe!
Function GetConnXLS(ByVal cFileName As String, _
Optional ByVal InformErrMSG As Boolean = False) As ADODB.Connection
On Error GoTo LOI:
Dim oConn As ADODB.Connection
Dim Ext As String, ConnStr As String
Set oConn = New ADODB.Connection
ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & cFileName & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;"";"
oConn.Open ConnStr
Set GetConnXLS = oConn
LOI:
If Err.Number 0 Then
Set oConn = Nothing
If InformErrMSG Then
MsgBox "GetConnXLS" & ": " & Err.Number & " " & Err.Description, vbCritical
End If
End If
End Function
Sub test()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sh As Worksheet
Dim I As Long
Set cnn = GetConnXLS(ThisWorkbook.Path & "\" & "test.xlsx")
If cnn Is Nothing Then
MsgBox "Check lai co so du lieu"""
Exit Sub
End If
Set rst = cnn.Execute("SELECT 1 FROM DATA")
Set sh = Sheets("Master")
For I = 0 To rst.Fields.Count - 1
sh.Cells(3, I + 1).Value = rst.Fields(I).Name
Next I
I = sh.Range("A4").CopyFromRecordset(rst)
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub
Sub TestOpenFile()
Dim files As Variant
files = Application.GetOpenFilename(, , , , True)
If VarType(files) = vbBoolean Then Exit Sub
Dim I As Long
For I = LBound(files) To UBound(files)
Next I
Debug.Print VarType(files)
Debug.Print TypeName(files)
End Sub
Sub merge_all()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sh As Worksheet
Dim I As Long, k As Long, CountFiles As Long, J As Long
Dim files As Variant
files = Application.GetOpenFilename(, , , , True)
If VarType(files) = vbBoolean Then Exit Sub
Set sh = Sheets("Master")
For k = LBound(files) To UBound(files)
Set cnn = GetConnXLS(files(k))
If cnn Is Nothing Then
MsgBox "Check lai co so du lieu file: " & files(k)
Exit Sub
End If
Set rst = cnn.Execute("SELECT *,""" & files(k) & """ as [From File] FROM DATA")
CountFiles = CountFiles + 1
If CountFiles = 1 Then
For J = 0 To rst.Fields.Count - 1
sh.Cells(3, J + 1).Value = rst.Fields(J).Name
Next J
End If
I = I + sh.Range("A" & 4 + I).CopyFromRecordset(rst)
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
Next k
MsgBox "Done"
End Sub