select and import text file with open file dialog in MS Excel 1

[seta42]

Hi,

as an nonprogrammer I recorded macro to import file from fixed position:

Sub import()
ChDir "D:\import"
Workbooks.OpenText Filename:="D:\import\OFFLINE.DAT", Origin:= _
852, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1)), DecimalSeparator:=".", TrailingMinusNumbers _
:=False
End Sub

even worse, I found (in WBA Help) how to show file path,

Sub Main()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
.Filters.Clear
.Filters.Add "All files", "*.*"
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
MsgBox "Path name: " & vrtSelectedItem
Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With
Set fd = Nothing
End Sub

but I dont know how to import selected file from Main() with all settings from Import() macro.
I have read posts both from VRoscioli and Fumei, I have tried to play arround a little, but it does not work. (Obviously, Iam not a golfer)
It is a "piece of cake" thing, but I have no experience in VBA.

Many Thanks
Seta42

 

[Chance1234]

Ok,

Take a look at this line here

Workbooks.OpenText Filename:="D:\import\OFFLINE.DAT",

now look at what main() does

MsgBox "Path name: " & vrtSelectedItem

What you need to do is pass vrtSelectItem to the sub import

So change

sub import() to sub import(str_filename as string)

then change
Workbooks.OpenText Filename:="D:\import\OFFLINE.DAT",

To
Workbooks.OpenText Filename:=" & str_filename & ",






Chance,

F, G + H

 

[Rofeu]

Chance,

shouldn't that be:

Workbooks.OpenText Filename:=str_filename,

Cheers,

Roel

 

[seta42]

Dear Chance and Rofeau

thanks for your lightning-fast responces,
but neither does not work.

1) when I changed Sub import() to Sub Import(str_filename)
I lost the import macro from macro list.

I also changed Workbooks.OpenText Filename:="D:\import\OFFLINE.DAT",
To Workbooks.OpenText Filename:=" & str_filename & ",

as you mentioned and now I have following situation:

Main() macro shows the dialog box with path and filename as before and when I tried to run the import macro (I had link it to the buton before changes), it returns following Microsoft visual basic pop-up window with:
"Argument not optional" error message.

Many thanks
Seta42

 

[Rofeu]

Hi,

call the Import from Main by adding this as the last line in Main:

 Call Import(str_filename)

Cheers,

Roel

 

[seta42]

Hi Roel,

this try to do something, but stops on
inserted line Call Import(str_filename) in Main() macro with:
"ByRef argument type mismatch" error message.

Could the problem be in different file types?
Dim vrtSelectedItem as Variant -in Main() macro
Sub Import(str_filename As String) -in Import

Thanks
Seta42

 

[Rofeu]

Hold on, I misread the code completely.

You need to pass vrtSelectedItem to Import so

Call Import(vrtSelectedItem)

Cheers,

Roel

 

[seta42]

Hi Roel,

It looks much more logical width vrtSelectedItem but,
unfortunatelly, I still got the same error message:

"ByRef argument type mismatch"

Thanks
Seta42

PS. runing XP Prof SP2 + Office 2k3

 

[Chance1234]

change

Dim vrtSelectedItem As Variant
Dim vrtSelectedItem as String



Chance,

F, G + _HH

 

[seta42]

Hi,

Dim vrtSelectedItem must be Variant or Object

Thaks Seta42

 

[Chance1234]

Sub OpenFileFrom()
    Dim FD As FileDialog
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    
    With FD
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        If .Show = -1 Then
            Call import(FD.SelectedItems(1))
        End If
        
        End With
        
End Sub

Sub import(strpath As String)



    Workbooks.OpenText Filename:=strpath, Origin:= _
        852, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1)), DecimalSeparator:=".", TrailingMinusNumbers _
        :=False
End Sub

This works fine on my machine,

Chance,

F, G + _HH

 

[seta42]

Dear Chance12345,

it works perfectly on my machine too .

Thank you very much for your time.

Seta42