How Do I modify this code to look at an external file to populate the dropdown list?

[kwiat]

Hello

I have this code that work with my ribbon control, I have a dropdown box that is populated by the text in the cells from A7:A100 on the active sheet I am on. I want to set it up so it will look at an external file (file can be txt, xls..etc) stored in a folder (does not open it) and pull the values in from that file. The setup below only works when I open this workbook but I am trying to set this up as an add in in a separate tab that will be populated even if no workbooks are open. Can I somehow set the path to the file in this code?


''=========Drop Down Code =========


''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.

Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)


With Sheet1.Range("A7:A100")
Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
ItemCount = ListItemsRg.Rows.Count
returnedVal = ItemCount
End With
End Sub

''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
returnedVal = ListItemsRg.Cells(index + 1).Value
''index is 0-based, our list is 1-based so we add 1.
End Sub

 

[Worf]

Not working, it keeps coming bad to debug on this line:

Set wr = ThisWorkbook.Worksheets("Sheet1").Range("a7:a10")

I set the path (Set wb = Workbooks.Open(ThisWorkbook.Path & "\logfile.xls")) to the file which is just "\ logfile.xls" and this files open then hangs up. Secondly, how would I set the full path if it was on another drive or location? I tried that inside the quotes and it hangs there.

• What´s the error message and number?
• I used the Path property because my main workbook and the log file are at the same folder.
  You can write the full path like in your previous post:
  

  Set wb = Workbooks.Open("C:\files\file.xls")

• I made a change to the RibbonX code, did you notice that?
• Note that at my example clicking for the first time at the OPEN FILES tab just brings the data from the log file to the active workbook. Clicking for the second time actually displays this new tab.
• Would you like me to post my test workbook online? It worked fine with Excel 2007.

 

[kwiat]

• What´s the error message and number?
• I used the Path property because my main workbook and the log file are at the same folder.
  You can write the full path like in your previous post:
  

  Set wb = Workbooks.Open("C:\files\file.xls")

• I made a change to the RibbonX code, did you notice that?
• Note that at my example clicking for the first time at the OPEN FILES tab just brings the data from the log file to the active workbook. Clicking for the second time actually displays this new tab.
• Would you like me to post my test workbook online? It worked fine with Excel 2007.

Let me play with it for a bit, I did not see you changed the ribbon code, will go back and grab it, that might be the issue. The error is 9 subscript out of range

I just checked, there is the broken image link after your VB code, can you post the xml code in between the html tags on your post

 

[Worf]

<!--?xml version="1.0"?-->This was designed to work together with the callback procedures of my other post. I tried it as an image before to keep the formatting...

- - - - -

Oops, did not work...

 

[Worf]

HTML tags in place...

<?xml version="1.0"?>-<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 -<ribbon> -<tabs> -<tab label="OPEN FILES" id="CustomTab">
 -<group label="FILES" id="FILES" insertAfterMso="GroupEditingExcel">
 <dropDown label="FILES" id="dd1" getSelectedItemIndex="DDItemSelectedIndex"
 onAction="DDOnAction" getItemLabel="DDListItem" getItemCount="DDItemCount"/>
 </group> </tab> </tabs> </ribbon> </customUI>

 

[Worf]

Hi Kwiat

I believe the next step should be you testing successfully the data transfer from the external workbook. So, please download my tested example.
Note that for this example the second file must be called source.xlsx and reside at the same folder.
After that, it's making it an add-in.

http://dl.dropbox.com/u/52116836/Ribbon_newtab.xlsm

 

[kwiat]

Hi Kwiat

I believe the next step should be you testing successfully the data transfer from the external workbook. So, please download my tested example.
Note that for this example the second file must be called source.xlsx and reside at the same folder.
After that, it's making it an add-in.

http://dl.dropbox.com/u/52116836/Ribbon_newtab.xlsm

WORF

I just downloaded and tested your code, it seems to work good. I even changed the path of the file and it still works, thanks – good work

My next step is to launch a template file into a new workbook, the templates could be within the source.xlsx as individual tabs. In the dropdown each item would be linked to a template, once launched all the data and text would be filled in. I will work on some template pages that will be launched.

 

[Worf]

My next step is to launch a template file into a new workbook, the templates could be within the source.xlsx as individual tabs. In the dropdown each item would be linked to a template, once launched all the data and text would be filled in. I will work on some template pages that will be launched.

The new version below will copy the templates from the source file, when the sheet name is clicked on the Ribbon. This file is visible for now, but when we make it an add-in it will become invisible.

Option Explicit


Dim ItemCount%, ListItemsRg As Range, MySelectedItem$, wb As Workbook, _
tw As Workbook, wr As Range, i%


''=========Drop Down Code =========, this goes at a regular module


''Callback for Dropdown getItemCount. Tells Excel how many items in drop down.


Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
Dim sn%


'Application.ScreenUpdating = False


' inform full path
Set wb = Workbooks.Open(ThisWorkbook.Path & "\source.xlsm")
Set tw = ThisWorkbook
tw.Activate


sn = 7          ' starting row to store sheet names
For i = 1 To wb.Sheets.Count
    tw.Worksheets("Sheet1").Cells(sn, 1).Value = wb.Sheets(i).Name
    sn = sn + 1
Next
Set wr = tw.Worksheets("Sheet1").Range("a7:a" & (sn - 1))


With wr
    Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
    ItemCount = ListItemsRg.Rows.Count
    returnedVal = ItemCount
End With


'wb.Close False
'Set wb = Nothing
Application.ScreenUpdating = True
End Sub


''Callback for dropdown getItemLabel. Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.


Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1).Value
    ''index is 0-based, our list is 1-based so we add 1.
    
End Sub


''Drop down change handler. Called when a drop down item is selected.


Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
Dim shname$(), res, twc%
' Two ways to set the variable MySelectedItem to the dropdown value


MySelectedItem = ListItemsRg.Cells(index + 1).Value         'way 1
twc = tw.Sheets.Count


ReDim shname(1 To twc)
For i = 1 To twc
    shname(i) = tw.Sheets(i).Name
Next


' copy template to new sheet, if not already present
If UBound(Filter(shname, MySelectedItem)) = -1 Then _
    wb.Worksheets(MySelectedItem).Copy after:=tw.Sheets(twc)


'way 2 is : Call DDListItem(control, index, MySelectedItem)


End Sub


''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value
End Sub


''------- End DD Code --------


''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem()


    MsgBox "The variable MySelectedItem has the value = " & MySelectedItem


End Sub

 

[kwiat]

OK This is getting close as I tested it and it works. A few thing I will need though, I have some other code I was testing and works a little but yours is much better.

1. The sheets are automatically opened, I want them linked to another ICON callback in the ribbon that will trigger it(at least for now) because there will be other variables that determine what data is extracted from the DB and into the template. For instance, I might want the data to be from today only or I might want to select a different days data. I may also want to select a rang of days. Additionally; another attribute might be the type of data that is pulled in, I might want to have it filter the data based on an average value or just the highest data from a certain date/time set.

2. I see you are adding a sheet to the list first of the list on the front sheet that will be used in the dropdown. I have a similar problem with my other code as it will not run unless there is a sheet in the workbook, this showed up when I saved and then loaded as an addin, when selecting the tab upon opening excel no sheets are available, when I selected a name in the list and executed it would through an error. It was only after I loaded a workbook and deleted sheets 2 & 3, leaving sheet 1 would it run. Another problem I have is it will only load one template, after that it thrown an error. my next reply will be the code.

The new version below will copy the templates from the source file, when the sheet name is clicked on the Ribbon. This file is visible for now, but when we make it an add-in it will become invisible.

Option Explicit


Dim ItemCount%, ListItemsRg As Range, MySelectedItem$, wb As Workbook, _
tw As Workbook, wr As Range, i%


''=========Drop Down Code =========, this goes at a regular module


''Callback for Dropdown getItemCount. Tells Excel how many items in drop down.


Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
Dim sn%


'Application.ScreenUpdating = False


' inform full path
Set wb = Workbooks.Open(ThisWorkbook.Path & "\source.xlsm")
Set tw = ThisWorkbook
tw.Activate


sn = 7          ' starting row to store sheet names
For i = 1 To wb.Sheets.Count
    tw.Worksheets("Sheet1").Cells(sn, 1).Value = wb.Sheets(i).Name
    sn = sn + 1
Next
Set wr = tw.Worksheets("Sheet1").Range("a7:a" & (sn - 1))


With wr
    Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
    ItemCount = ListItemsRg.Rows.Count
    returnedVal = ItemCount
End With


'wb.Close False
'Set wb = Nothing
Application.ScreenUpdating = True
End Sub


''Callback for dropdown getItemLabel. Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.


Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1).Value
    ''index is 0-based, our list is 1-based so we add 1.
    
End Sub


''Drop down change handler. Called when a drop down item is selected.


Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
Dim shname$(), res, twc%
' Two ways to set the variable MySelectedItem to the dropdown value


MySelectedItem = ListItemsRg.Cells(index + 1).Value         'way 1
twc = tw.Sheets.Count


ReDim shname(1 To twc)
For i = 1 To twc
    shname(i) = tw.Sheets(i).Name
Next


' copy template to new sheet, if not already present
If UBound(Filter(shname, MySelectedItem)) = -1 Then _
    wb.Worksheets(MySelectedItem).Copy after:=tw.Sheets(twc)


'way 2 is : Call DDListItem(control, index, MySelectedItem)


End Sub


''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value
End Sub


''------- End DD Code --------


''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem()


    MsgBox "The variable MySelectedItem has the value = " & MySelectedItem


End Sub

 

[kwiat]

here is the code I was working with, at the bottom the sub GenerateLog is the callback I used when I am ready to generate the log.

Option Explicit

Dim ItemCount%, ListItemsRg As Range, MySelectedItem$
Dim PathName As String
Dim Filename As String
Dim TabName As String
Dim ControlFile As String


''=========Drop Down Code =========


''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.


Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
Dim wr As Range, wb As Workbook


Application.ScreenUpdating = False


' inform full path
Set wb = Workbooks.Open("C:\Add Ins\source.xlsm")


Set wr = ThisWorkbook.Worksheets("Sheet1").Range("a7:a100")
' transfer data from other workbook
wr.Value = wb.Worksheets("Sheet1").Range("a7:a100").Value


With wr
    Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
    ItemCount = ListItemsRg.Rows.Count
    returnedVal = ItemCount
End With


wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
End Sub


''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1).Value
    ''index is 0-based, our list is 1-based so we add 1.
    'Workbooks("Logfile.XLS").Close SaveChanges:=False
End Sub


''Drop down change handler.
''Called when a drop down item is selected.
Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
' Two ways to set the variable MySelectedItem to the dropdown value


'way 1
    MySelectedItem = ListItemsRg.Cells(index + 1).Value


    ''way 2
    'Call DDListItem(control, index, MySelectedItem)


End Sub


''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value
End Sub


''------- End DD Code --------




''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem(control As IRibbonControl)
    MsgBox "The variable MySelectedItem have the value = " & MySelectedItem & vbNewLine & _
           "You can use MySelectedItem in other code now to use the dropdown value"
End Sub


Sub GenerateLog(control As IRibbonControl)
    ' This macro will import a file into this workbook
    ' Sheets("Sheet1").Select
    PathName = "C:\Add Ins\"
    Filename = "source.xlsm"
    TabName = MySelectedItem
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:=PathName & Filename
    ActiveSheet.Name = TabName
    Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
    Windows(Filename).Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate
End Sub

 

[kwiat]

Hey Worf

I just test your code as an addin and it automatically opens all the templates, I need it to only open when it is selected in the dropdown and then executed as it will overload the program with too much data if there are allot of templates or logs to open.