User Open CSV Into Tab Using VBA

Question

I am trying to a button that when pressed prompts the user to open a csv file and then a bunch of other things are automatically performed on the csv file. Here is code that I am currently working with:

Sub MainMacro()

Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath

'Sheets.Add After:=Sheets(Sheets.Count)

'ActiveSheet.name = fNameAndPath

Call Macro1
Call Macro2
Call Macro3
Call Macro4
End Sub

Put this opens up the csv file and I want it to open in the tab and so the Marco1 Marco2... can perform their respective tasks. So the button would perform all of MainMacro() on the newly active sheet which the user then loaded in.

So you need to add a button to your workbook and call `MainMacro` from its `_Click` event? I'm not seeing what it is you're struggling with, adding a button? referencing the MainMacro code? Can you clarify what you're having trouble with please? — Dave, Jun 09 '16 at 16:27
@Dave I have a button and I assigned it the macro "MainMacro". The file opens up in a different window, but I would like it to open up as a sheet next to the sheet that has the button on it. — Adam Warner, Jun 09 '16 at 16:29
Right, ok, I understand. Try using `Workbooks.OpenText` instead of `Workbooks.Open` and let me know if that solved your problem? — Dave, Jun 09 '16 at 16:32
Check here for more info on the command: https://msdn.microsoft.com/en-us/library/aa195814(v=office.11).aspx — Dave, Jun 09 '16 at 16:33
Does this answer help at all? http://stackoverflow.com/a/12197937/6255978 Failing that, you can modify the code to simply copy the worksheet from the newly opened workbook into your original workbook before you call the various macros — Dave, Jun 09 '16 at 16:42
@Dave it does help except it puts the data in a weird format — Adam Warner, Jun 09 '16 at 17:20

score 2 · Accepted Answer · answered Jun 09 '16 at 17:31

This code will open the csv and then copy the worksheet to your original workbook for you to do with as you will. On copying the sheet to the destination workbook, the copied sheet will also activate.

Sub MainMacro()
    Dim csvWB As Workbook
    Dim fNameAndPath As Variant
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Set csvWB = Workbooks.Open(Filename:=fNameAndPath)
    csvWB.Worksheets(1).Copy Before:=ThisWorkbook.Worksheets(1)
    csvWB.Close
    Call Macro1
    Call Macro2
    Call Macro3
    Call Macro4
End Sub

Of course, depending on what Macro1-4 actually do, it may be easier to open it in a different workbook and just perform your Macros against that?

User Open CSV Into Tab Using VBA

1 Answers1