Macro modification

HotLanta

Board Regular
Joined
Nov 3, 2005
Messages
173
Hi,

I have a macro that works wonderfully and I wanted to modify it to do something else. I am having trouble with the modification and I wanted some insight on whether I should continue on the path I am on or start with something new.

I currently have (for my coworkers ease of use) a macro enabled workbook with a one click button. After the click the macro does the following:

Macro enabled workbook -> New workbook is created and input box asks user to select text file (CSV). Text file is opened and data is processed -> Processed data is copied to the new workbook -> CSV file is closed and Macro enabled workbook is closed.

For my new project I want to be able to open CSV file 1 and process the data, then open CSV file 2 and process its data, then marry CSV1 and CSV2 data together on the new workbook.

I have the macro worked out on the marrying of CSV1 and CSV2 data. My trouble is I get terribly confused switching between workbooks and worksheets in macros and I'm not sure what is the best approach. It would be easier in my mind to copy the data from CSV1 and CSV2 to the new workbook and process it there. I'm having trouble with that because the worksheet names will never be the same in CSV1 and CSV2.

It seems most of my trouble is dealing with the names in VBA but also asking for a second CSV to be opened and processed.

I thought twice about posting, I realize I'm not asking an easy or direct question. I just wanted some insight on what might be the best approach.

Thanks in advance for any ideas.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi there. Yes it can be horribly confusing! The main thing to do is create some qualified references so that you can always be sure of referencing the correct workbook/worksheet. In this code I have set variables for your main workbook, CSV1 and CSV2 - just use a fully qualified reference each time and it should be a lot clearer.
VBA Code:
Sub HotLanta()
Dim CSV1 As Workbook
Dim CSV2 As Workbook
Dim MainBook As Workbook
Dim MainSheet As Worksheet
Dim CSV1Sheet As Worksheet
Dim CSV2Sheet As Worksheet
Set MainBook = ActiveWorkbook
Set MainSheet = ActiveWorkbook.ActiveSheet
Workbooks.OpenText "C:\development\thisisacsv.csv"
Set CSV1 = ActiveWorkbook
Set CSV1Sheet = CSV1.ActiveSheet
'  now do what you want with CSV1 always using MainSheet or CSV1 as necessary
Workbooks.OpenText "C:\development\anothercsv.csv"
Set CSV2 = ActiveWorkbook
Set CSV2Sheet = CSV2.ActiveSheet

' and from then on always qualify each reference in your code with Mainbook.sheets("ffgh").    or CSV1.Sheets("bgjk").  etc.

End Sub
 
Upvote 0
Hi John,

I have tested and made a few modifications to the above and it is working great... except... We often work from network drives. Having a static location for the csv files won't work for the other users.

Instead of - Workbooks.OpenText "C:\development\thisisacsv.csv", can the above be modified so a pop up will ask the user to select the location CSV1 and CSV2 or stored?
 
Upvote 0
Hi there. Try this (if your csv's are true .CSV files, then remove the other xls file types):
VBA Code:
Sub HotLanta()
    Dim CSV1 As Workbook
    Dim CSV2 As Workbook
    Dim MainBook As Workbook
    Dim MainSheet As Worksheet
    Dim CSV1Sheet As Worksheet
    Dim CSV2Sheet As Worksheet
    Dim PathName1 As String
    Dim PathName2 As String
    Set MainBook = ActiveWorkbook
    Set MainSheet = ActiveWorkbook.ActiveSheet
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "CSV Files", "*.csv; *.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
        'Store in fullpath variable
        PathName1 = .SelectedItems.Item(1)
    End With
    
    Workbooks.OpenText PathName1
    Set CSV1 = ActiveWorkbook
    Set CSV1Sheet = CSV1.ActiveSheet
    '  now do what you want with CSV1 always using MainSheet or CSV1 as necessary
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "CSV Files", "*.csv; *.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
        'Store in fullpath variable
        PathName2 = .SelectedItems.Item(1)
    End With
    
    Workbooks.OpenText PathName2
    Set CSV2 = ActiveWorkbook
    Set CSV2Sheet = CSV2.ActiveSheet

    ' and from then on always qualify each reference in your code with Mainbook.sheets("ffgh").    or CSV1.Sheets("bgjk").  etc.

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top