Tuurlijk Michel,
Ik heb wel gemerkt dat de variabele L niet herkend wordt omdat deze in de functie ImportFA() gedeclareerd is maar globaal declareren is toch rommelig programmeren? Nu zit ik hopeloos vast
Ik heb wel gemerkt dat de variabele L niet herkend wordt omdat deze in de functie ImportFA() gedeclareerd is maar globaal declareren is toch rommelig programmeren? Nu zit ik hopeloos vast
Code:
Option Compare Database
Option Explicit
Private Const FaDelim As String = "|"
Private Const FaVelden As Byte = 11
Public Function ImportFA() As Boolean
Dim fd As FileDialog, strBestand As String
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogOpen)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
'Dim varSelectedItem As Variant
'Use a With...End With block to reference the FileDialog object.
With fd
'Set the initial path to the drive where the file exists.
.InitialFileName = "K:\Ambertje\"
'Don't allow the selection of multiple files.
.AllowMultiSelect = False
'Change the title of the dialog
.Title = "Kies het FA bestand: "
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
Select Case .Show
Case False
Set fd = Nothing
Exit Function
End Select
'Step through each string in the FileDialogSelectedItems collection
'For Each varSelectedItem In .SelectedItems
' MsgBox "Bestand " & varSelectedItem & " is geselecteerd"
' Next varSelectedItem
strBestand = .SelectedItems(1)
End With
Set fd = Nothing
'Feitelijk inlezen van het bestand:
Dim fso As FileSystemObject, f As TextStream, varData(), tmp As Variant
Dim fts As Scripting.TextStream
Dim L As Long
Dim i As Integer, strLijn() As String
'The following code illustrates how the FileSystemObject is used to return a TextStream object that can be read from or written to:
Set fso = New FileSystemObject
Set fts = fso.OpenTextFile(strBestand)
'per record wordt de data veld per veld ingevuld
'Store|FTT|Route|Stop|FSP|Aant_Pal|Sel_Method|Total_Volume|Total_Weight|Aant_Prod|Aant_Collis
'10544|KKK|3702 | 1 |400| 1 | S | 0.239 | 74.70 | 26 | 28
'10544|YYY|3702 | 1 |400| 1 | S | 0.487 | 135.36 | 41 | 56
With fts
Do Until .AtEndOfStream
ReDim Preserve varData(0 To (FaVelden - 1), 0 To L)
tmp = .ReadLine
If Trim(tmp) = "" Then
Call ImporteerNaarTabel
End If
strLijn = VBA.Split(tmp, "|")
For i = 0 To (FaVelden - 1)
varData(i, L) = strLijn(i)
Next
L = L + 1
Loop
.Close
End With
Set fts = Nothing
Set fso = Nothing
End Function
Public Function ImporteerNaarTabel()
'Updaten van Tbl_Fa
Dim strSQL As String, rst As DAO.Recordset DBEngine.BeginTrans
On Error GoTo FOUT
strSQL = "DELETE * from Tbl_Fa"
CurrentDb.Execute strSQL
strSQL = "SELECT * from Tbl_Fa where 0 = 1"
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
For L = LBound(varData, 2) To UBound(varData, 2)
.AddNew
.Fields("Store").Value = varData(0, L)
.Fields("FTT").Value = varData(1, L)
.Fields("Route").Value = varData(2, L)
.Fields("Stop").Value = varData(3, L)
.Fields("FSP").Value = varData(4, L)
.Fields("Aant_Pal").Value = varData(5, L)
.Fields("Sel_Method").Value = varData(6, L)
.Fields("Total_Volume").Value = varData(7, L)
.Fields("Total_Weight").Value = varData(8, L)
.Fields("Aant_Prod").Value = varData(9, L)
.Fields("Aant_Collis").Value = varData(10, L)
.Update
Next
End With
DBEngine.CommitTrans
ImportFA = True
MsgBox "Import pbl MSI succesvol"
Exit Function
FOUT:
DBEngine.Rollback
MsgBox "Probleem met de import ! Niets geïmporteerd." + vbCrLf + _
VBA.Err.Description
End Function
[/CODE
Groetjes,
Ambertje
