I need a Macro or formula that identifies external source (csv) file name

[ksouza]

I have an Excel 2007 file with an external link to a csv. Users of my file can choose to refresh the csv file and they can select from a list of csv's in a pre-populated library.
How do I show the user which CSV file name they have selected? I want to display this file name in the headers of reports that are generated from the CSV.
In short, is there a macro or formula that identifies the name of the CSV file that is currently selected in the external data connections?
Thanks for reading.

-K Souza
<!-- / message -->

 

[Rasm]

How does the user select the CSV file - seems if you popualte a listbox or a combobox with the available filenames - then let him select from there - now you will have a .ListIndex that tracks which file is being used.

 

[ksouza]

Hi Rasm,
Thanks for your interest in my problem!
The users refresh the CSV's by initiating the simple code below. The macro goes to the "Rebates" tab where the CSV resides. Clicks on a cell within the CSV range (B17). And pushes the "refresh" button. From there the user is prompted to select from a list of CSV's (it looks and feels similar to how a user would open an file from Excel). I have four CSV's to refresh. Below shows two of them.

Sheets("Rebates").Select
Range("B17").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Sheets("Rebates Prior").Select
Range("B17").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

I am not familiar with using a listbox or combobox for this purpose. I have only used a combobox once before for something simple. Any sample code would be most appreciated.

Regards,
-K Souza

 

[Rasm]

Try this - I have simply added a combobox & a label to the sheet - I used of the ActiveX type. You will notice AddItem property - that is how to fill the combobox - a listbox works the same way - other useful properties are

.ListIndex - this is the element in the array - remember the first one is Zero- 2nd is 1 and so on - Listindex of -1 means you have not selected an item

.Listcount = how may elements

Anyway - when in code section just hit F2 - now you get a list of all valid proterties

You will have to fill the combobox with whatever list you of CsV files you have - but sounds like you are using the windows API - so you get array from there.


Thisworkbook

Option Explicit

Private Sub Workbook_Open()
     Call MakeListOfCSV
End Sub

Sheet Rebates

Option Explicit
Private Sub ComboBox1_Change()
    Label1.Caption = ComboBox1.Text
End Sub
Public Sub MakeListOfCSV()
   With Sheets("Rebates").ComboBox1
        .Clear
        .AddItem "CSV file 1"
        .AddItem "CSV file 2"
        .AddItem "CSV file 3"
        .ListIndex = 0
    End With
    
End Sub

 

[ksouza]

Hi,

Thanks for the combo box idea. I am able to populate the combo box by linking to a list of file names. Changing the combo box triggers the refresh of the csv. The combo box selection can then be used in my report headers.
The only concern is that a user could select "2011 2.1 Rebates" in the combo box and then go select "2011 1.1 Rebates" in the list of CSVs. Should I have a hyperlink from the Combo Box selection to the desired CSV?
Below is my code. I am only coding 2 CSV's for now. I will end up with 30+ CSV's over the course of the year.


Private Sub Rebates_Change()
Select Case Rebates.Text
Case "2011 1.1 Rebates"
Sheets("Rebates").Visible = True
Sheets("Rebates").Select
ActiveSheet.Cells(17, 17).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Rebates").Visible = False
Sheets("Criteria").Select
ActiveSheet.Cells(1, 1).Select

Case "2011 2.1 Rebates"
Sheets("Rebates").Visible = True
Sheets("Rebates").Select
ActiveSheet.Cells(17, 17).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Rebates").Visible = False
Sheets("Criteria").Select
ActiveSheet.Cells(1, 1).Select
Calculate
Application.CutCopyMode = False


Case Else
End Select
Sheets("Criteria").Range("I1").Value = Rebates.Value
Sheets("Criteria").Range("I2").Value = RebatesPrior.Value

 

[Rasm]

I dont know what you do in your code - but seems in both cases you reference the same cells (red below) - if they are truely the same why even use a select Case - but it sounds like a coding issue.


Private Sub Rebates_Change()
Select Case Rebates.Text
Case "2011 1.1 Rebates"
Sheets("Rebates").Visible = True
Sheets("Rebates").Select
ActiveSheet.Cells(17, 17).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Rebates").Visible = False
Sheets("Criteria").Select
ActiveSheet.Cells(1, 1).Select

Case "2011 2.1 Rebates"
Sheets("Rebates").Visible = True
Sheets("Rebates").Select
ActiveSheet.Cells(17, 17).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Rebates").Visible = False
Sheets("Criteria").Select
ActiveSheet.Cells(1, 1).Select
Calculate
Application.CutCopyMode = False


Case Else
End Select
Sheets("Criteria").Range("I1").Value = Rebates.Value
Sheets("Criteria").Range("I2").Value = RebatesPrior.Value

 

[ksouza]

ActiveSheet.Cells(17, 17).Select
This is a cell within the range of the CSV data. It is the equivalent of going to the "Rebates" tab, clicking on a cell within the CSV data, right clicking, and pushing "refresh".

ActiveSheet.Cells(1, 1).Select
I like to end the code with the active cell being in the top left. There is nothing else going on here.

 

[Rasm]

I am not sure I follow - sorry - was that code inside of the ComboBox1_change - not sure what you called the comboBox - not sure if this will help you - sorry

Private Sub ComboBox1_Change()
    Select Case ComboBox1.Text
        Case "2011 1.1 Rebates"
            Sheets("Rebates").Visible = True
            Sheets("Rebates").Select
            ActiveSheet.Cells(17, 17).Select
            Selection.QueryTable.Refresh BackgroundQuery:=False
            Sheets("Rebates").Visible = False
            Sheets("Criteria").Select
            ActiveSheet.Cells(1, 1).Select
        Case "2011 2.1 Rebates"
                Sheets("Rebates").Visible = True
                Sheets("Rebates").Select
                ActiveSheet.Cells(17, 17).Select
                Selection.QueryTable.Refresh BackgroundQuery:=False
                Sheets("Rebates").Visible = False
                Sheets("Criteria").Select
                ActiveSheet.Cells(1, 1).Select
                Calculate
                Application.CutCopyMode = False
        Case Else
            MsgBox ("Cannot find selection")
    End Select
End Sub

 

[ksouza]

Thanks for your help!! Your combo box suggestion got me on a better track. My code is not perfect but i can go with it. Thanks again for the help. Much appreciated!

-K