Import txt file with open file dialog box and break them into cells

Question

I Know I am doing a mistake after the line: If intChoice <> 0 Then
Can someone help me to rectify it?

Private Sub CommandButton1_Click()
    Dim intChoice As Integer
    'Select the start folder
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "I:\Dunnings"
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'determine what choice the user made

    If intChoice <> 0 Then
        Workbooks.OpenText.Filename:= intChoice, Origin:=xlMSDOS, StartRow:=23, DataType:=xlFixedWidth, FieldInfo:= _
            Array(Array(0, 1), Array(6, 2), Array(23, 1), Array(30, 2), Array(63, 2), Array(68, 1), _
            Array(77, 4), Array(88, 4), Array(101, 1), Array(117, 1)), TrailingMinusNumbers:= _
            True
        NewPath = Mid(ThisWorkbook.FullName, 1, _
        Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\" & _
            "Dunnings - " & Format(Date, "dd-mm-yyyy") & ".xlsm"
        ThisWorkbook.SaveAs (NewPath)
    End If
End Sub

It is in the line after If intChoice <> 0... Cant use that variable intChoice there... — Isu, Jan 15 '15 at 06:25

score 1 · Answer 1 · edited Jan 16 '15 at 22:38

Private Sub CommandButton1_Click()
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "I:\"
        .Filters.Clear
        .Title = "Your Title"
        If Not .Show Then
            MsgBox "No file selected.": Exit Sub
        End If
        Workbooks.OpenText .SelectedItems(1), Origin:=xlMSDOS, StartRow:=23, _
                DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 2), _
                Array(23, 1), Array(30, 2), Array(63, 2), Array(68, 1), _
                Array(77, 4), Array(88, 4), Array(101, 1), Array(117, 1)), _
                TrailingMinusNumbers:=True
        NewPath = Mid(ThisWorkbook.FullName, 1, _
            Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\" & _
            "Dunnings - " & Format(Date, "dd-mm-yyyy") & ".xlsm"
        ThisWorkbook.SaveAs (NewPath)
    End With
End Sub

Basically the rest of the code can be recorded using RecordMacro and then copy resulting code to your VB code.

When I try this same technique in a new file, it come up with an error,... NewPath = Mid(ThisWorkbook.FullName, 1, _ Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\" & _ "Dunnings - " & Format(Date, "dd-mm-yyyy") & ".xlsm" ThisWorkbook.SaveAs (NewPath) Same error as this question attached http://stackoverflow.com/questions/17173898/how-to-do-a-save-as-in-vba-code-saving-my-current-excel-workbook-with-datesta — Isu, Jan 20 '15 at 23:16

L42 · Accepted Answer · 2015-01-15T06:59:40.180

0

IntChoice is integer.
Filename argument expects string. Something like this:

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "I:\Dunnings"
    .Filters.Clear
    .title = "Your Title"
    If Not .Show Then
        MsgBox "No file selected.": Exit Sub
    End If
    Workbooks.OpenText .SelectedItems(1) 'and the rest of your code.
End With

edited Jan 15 '15 at 06:59

answered Jan 15 '15 at 06:31

L42

19,427
11
44
68

Yep I know that, rather than calling a file on a specific location, I need to call the file I select on the top part.. That's where I struggle... I am not good at VBA... That's is the main issue for this.... – Isu Jan 15 '15 at 06:34
Your suggestion worked perfectly for me and I obtained results I want. Thx a lot. This is my final result if any one needs it in the future... – Isu Jan 16 '15 at 06:21
When I try this same technique in a new file, it come up with an error,... NewPath = Mid(ThisWorkbook.FullName, 1, _ Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) & "\" & _ "Dunnings - " & Format(Date, "dd-mm-yyyy") & ".xlsm" ThisWorkbook.SaveAs (NewPath) Same error as this question attached http://stackoverflow.com/questions/17173898/how-to-do-a-save-as-in-vba-code-saving-my-current-excel-workbook-with-datesta – Isu Jan 20 '15 at 23:19

Import txt file with open file dialog box and break them into cells

2 Answers2

Linked