hallo rasta
hab jetzt versucht deine lösungsvorschläge zu übernehmen..da tauchen aber immer noch probleme auf *schäm*
mein jetziger code:
Private Const Pfad = "M:\bcs\Prozessoptimierung_BCS-SAP-Support\TestDB.mdb"
Private Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" (lpBrowseInfo As BrowseInfo) As Long
Public Function VerzeichnisWählen(Optional DialogTitel) As String
Dim StrukturVerzeichnisInfo As BrowseInfo, ListenNr As Long, Pfad As String
With StrukturVerzeichnisInfo
.hOwner = hWndAccessApp
.lpszTitle = IIf(IsMissing(DialogTitel), "Verzeichnispfad auswählen", CStr(DialogTitel))
.ulFlags = &H1
End With
ListenNr = SHBrowseForFolder(StrukturVerzeichnisInfo)
Pfad = Space$(512)
If SHGetPathFromIDList(ByVal ListenNr, ByVal Pfad) Then VerzeichnisWählen = Left(Pfad, InStr(Pfad, vbNullChar) - 1)
End Function
Private Sub CommandButton1_Click()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sConnString As String
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Pfad & ";Persist Security Info=False"
conn.Open sConnString
Set cmd.ActiveConnection = conn
cmd.CommandText = "Insert into Daten values('Request-ID','User-ID','Caretaker','Requester','Country Site','Company','Priority','Topic','Process','Date (Creation)','Date (closed)','Problem Origin','Short Description','Status','Forwarded to','Complexity','Cycle time (open)','Cycle time (pending)','Cycle time (forwarded)','Cycle time (in process)','CRT','CRT Number','Cycle Time (CRT)','Request by Phone','Material-Number')"
cmd.CommandType = adCmdText
'Set rs = cmd.Execute
Dim RequestID As Long
Dim UserID As Long
Dim Caretaker As String
Dim Requester As String
Dim CountrySite As String
Dim Company As String
Dim Priority As String
Dim Topic As String
Dim process As String
Dim Date_Creation As Long
Dim Date_closed As Long
Dim ProblemOrigin As String
Dim ShortDescription As String
Dim Status As String
Dim Forwarded_to As String
Dim Complexity As String
Dim Cycle_time_open As Long
Dim Cycle_time_pending As Long
Dim Cycle_time_forwarded As Long
Dim Cycle_time_in_process As Long
Dim CRT As Long
Dim CRT_Number As Long
Dim Cycle_time_CRT As Long
Dim Request_by_Phone As Long
Dim MaterialNumber As Long
Dim zeile As Long
Dim spalte As Long
spalte = 1
zeile = 1
Do Until Cells(zeile, spalte) = ""
RequestID = Str(Cells(zeile, spalte))
UserID = Str(Cells(zeile, spalte))
Caretaker = Cells(zeile, spalte)
Requester = Cells(zeile, spalte)
CountrySite = Cells(zeile, spalte)
Company = Cells(zeile, spalte)
Priority = Cells(zeile, spalte)
Topic = Cells(zeile, spalte)
process = Cells(zeile, spalte)
Date_Creation = Str(Cells(zeile, spalte))
Date_closed = Str(Cells(zeile, spalte))
ProblemOrigin = Cells(zeile, spalte)
ShortDescription = Cells(zeile, spalte)
Status = Cells(zeile, spalte)
Forwarded_to = Cells(zeile, spalte)
Complexity = Cells(zeile, spalte)
Cycle_time_open = Str(Cells(zeile, spalte))
Cycle_time_pending = Str(Cells(zeile, spalte))
Cycle_time_forwarded = Str(Cells(zeile, spalte))
Cycle_time_in_process = Str(Cells(zeile, spalte))
CRT = Str(Cells(zeile, spalte))
CRT_Number = Str(Cells(zeile, spalte))
Cycle_time_CRT = Str(Cells(zeile, spalte))
Request_by_Phone = Str(Cells(zeile, spalte))
MaterialNumber = Str(Cells(zeile, spalte))
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = CurrentProject.Connection
With cmd1
.CommandText = "INSERT INTO table1 (Name) VALUES ('Daten')"
.CommandType = adCmdText
.Execute
End With
Set cmd1 = Nothing
zeile = zeile + 1
Loop
End Sub
Private Sub VerzeichnisSuchen_Click()
Dim Pfad As String
Pfad = VerzeichnisWählen("Verzeichnis mit Access-Datenbanken")
If Pfad <> "" Then Me.SuchPfad = Pfad
End Sub
Sobald ich auf den button "suchen" klicke, öffnet sich das verzeichnis in access und net in excel.
es werden im verzeichnis außerdem nur die ordner angezeigt, aber net die files. also ich kann die exceldateien net auswählen.
bei dem befehl "conn.Open sConnString" zeigt er mir die meldung an:
operation is not allowed when the object is open.
hilfe! |