CODEWITHSUNDEEP
vbaexcelcopy-paste
darka
darka

Reputation: 37

Excel vba user to select workbook then copy data

I have a macro that will copy data from a csv file to my excel file and works great if the file name is the same every time. That's where the problem lies: it's not the same name every time.

I need the script to allow a user to select the csv file. Then, the code to allow it to copy. This is what I have:

Sub importmix()

    Worksheets("mixdata").Range("A1:P300").Clear

    '## Open workbooks first:
    Set X = Workbooks.Open("C:\test\mix.csv")
    '## Set values between workbooks
    Workbooks("2.xlsm").Worksheets("mixdata").Range("A1:K300").Value = _
        Workbooks("mix.csv").Worksheets("mix").Range("C1:M300").Value

    '##Close x:
    X.Close False
End Sub

Upvotes: 1

Views: 3989

Answers (2)

interesting-name-here
interesting-name-here

Reputation: 1890

I think you are looking for something like this:

Sub test()
    Dim intResult As Integer
    Dim fD As FileDialog

    Set fD = Application.FileDialog(msoFileDialogFilePicker)

    With fD
        .Title = "Select a Path"
        .AllowMultiSelect = False
        .Show
    End With

    importmix fD.SelectedItems(1)

End Sub

Sub importmix(path As String)

    Worksheets("mixdata").Range("A1:P300").Clear

    '## Open workbooks first:
    Set X = Workbooks.Open(path)
    '## Set values between workbooks
    Workbooks("2.xlsm").Worksheets("mixdata").Range("A1:K300").Value = _
        Workbooks(Dir(path)).Worksheets(Dir(Replace(UCase(path), ".csv", ""))).Range("C1:M300").Value

    '##Close x:
    X.Close False

End Sub

Dir(path) gives you the file name. A csv automatically opens a sheet with the name of the file without the extension so removing the .csv takes care of that.

Upvotes: 1

Cyril
Cyril

Reputation: 6829

Should be able to use: 

Dim intResult as integer
'Dialogue box name
Application.FileDialog(msoFileDialogFolderPicker).Title = "Select a Path"
'The dialog is displayed to the user
intResult = Application.FileDialog(msoFileDialogFolderPicker).Show

Upvotes: 0

Related Questions