Importing from a text box containing submission from multiselect dialog box. (1 Viewer)

[adammitchell23]

Hi folks,

This is a bit of a run-one from a previous post but I thought it was enough of a tangent to start a new thread.

I'm using the following code to enter filepaths from a dialog box into a text box on a form, which then tells an import routine which file to import using the transfertext method.

Private Sub cmdSelectFile_Click()


Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd

.AllowMultiSelect = True



If .Show Then
For i = 1 To fd.SelectedItems.Count
        Me.txtFileName.Value = Me.txtFileName.Value & ";" & .SelectedItems.Item(i)
    Next

End If

End With

End Sub



Public Function ImportWeather()

DoCmd.OpenForm "frmImport2", , , , , acDialog

RunImportProcedure Forms("frmImport2").Filename


    DoCmd.TransferText acImportDelim, "ImportWeather", "tblWeatherImportTemp", Filename, True
   

End Function

Public Sub RunImportProcedure(ByVal Filename As String)

MsgBox "Importing your files..."

End Sub

Public Property Get Filename() As String
Filename = Me.txtFileName.Value
End Property




    
Private Sub clrList_Click()
    Call clearListBox
    Me!mySelections.Value = Null
End Sub

This works fine if I'm only trying to import a single file but if I multiselect, Access tries to read the multiple file paths as a single string in the text box and fails. I thought the semicolon would let it know it needs to be importing multiple files, but it seems I'm wrong...

Thanks all,

Ad

 

[Galaxiom]

It is seeing them as one name because you are concatenating the filenames into the txtbox.

You need to loop through the SelectedItems and process the import on each loop.

If you really want to display them before the import you would need to use the loop to populate the RowSource of a listbox or recordsource of a subform then loop though them.

 

[arnelgp]

you modify ImportWeather() function

Public Function ImportWeather()
	Dim var As Variant
	Dim varFileNames As Variant
	varFileNames = Split(Me.txtFileName.Value,";")
	DoCmd.OpenForm "frmImport2", , , , ,acDialog
	For Each var In varFileNames
		DoCmd.TransferText acImportDelim, "ImportWeather","tblWeatherImportTemp", var, True
		DoEvents()
		RunImportProcedure var
	Next
	DoCmd.Close acForm, "frmImport2"
End Function

Public Sub RunImportProcedure(Byval Filename as string)
Dim WshShell
Dim strMsg As String
Set WshShell = CreateObject("wscript.shell")
strMsg=Filename & " has been imported."
WshShell.PopUp strMsg, 1, "Text Import", vbOKOnly + vbInformation
End Sub

 

[adammitchell23]

Ah, I see; Arnel, I'm getting a syntax error at

DoEvents()

 

[arnelgp]

remove the ()

 

[adammitchell23]

OK, done... but now it won't accept the filename argument in transfertext...

 

[isladogs]

Does the temp table exist?

 

[adammitchell23]

Does the temp table exist?

It does indeed!

 

[arnelgp]

because of importexportspec, remember
your first thread. how you resolved that,
i don't know. but whatever method
you do insert that to the actual import
process.

 

[adammitchell23]

Sorry, you've lost me... the import specification still works fine if I run that from the access interface. That's the specification "ImportWeather" in transfertext.

Sorry if I'm being really stupid... I've been stuck on this for the last week and it's driving me up the wall! :banghead: