Log In
Register
Home
Power Query
January 30, 2020
Offline
Afternoon,
Im wondering, as I am a intermediate experienced custom UI programmer, I have been using the following code in my own database for a user to select a button on excel and choose a excel file from a folder using msoFileDialogOpen function.
Dim sFolder As String
Dim OpenBook As Workbook
' Open the select folder prompt
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "H:\PROJECT-OPS\NSW Warehouse\NSWTA Inventory Listing"
I have got this sample workbook where on the ribbon "MY MACROS" and on the tile "Macro Lists" dropdown box Macro List 1 is taking the list data from name manager "MacroList' in the Admin TAB. (Highlighted in yellow).
Is there a way in either custom UI or in Sub myMacroDD(ByVal control As IRibbonControl. to select the listed macro list 1 (in admin) tab copy that selected item from dropdown and paste it to a cell on the same tab
Thanks
Steve
See AnswerPower Query
January 30, 2020
Offline
Just an update,
I just changed the following from .Value to .Copy
strMacro = ThisWorkbook.Names(strList) _
.RefersToRange.Rows(CLng(selectedIndex + 1)).Copy
Sheet1.Range("L3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Thanks
Steve
November 8, 2013
Offline
To add a dynamic list in ribbon, you have to use the dynamicMenu element in UI xml, text file attached. you'll have to get the list of files and build the dynamic menu.
To loop through a folder to get the list of files, i guess you know how to do it, here is an example: https://www.mrexcel.com/board/.....a.1126467/
The other question is not clear, you mentioned that you want to get the value selected in the dropdown, but you already have the code:
strMacro = ThisWorkbook.Names(strList) _
.RefersToRange.Rows(CLng(selectedIndex + 1)).Value
Answers Post
Power Query
January 30, 2020
Offline
Thanks Catalin for the advise , Ill have to study the zip file you gave me!.
In regards to your comment.
The .Value was a Application.Run (strMacro) that was taking the "value" of the name list of the 3 items (ToggleHeadings . Ect...). Once the dropdown was selected it was using that value to select the macro Sub ToggleHeadings().
I figured out, that if I used =IFERROR(@INDEX(GetFileNames($F$1),ROW()-6),"") in Cell F7 and using H:\PROJECT-OPS\NSW Warehouse\NSWTA Inventory Listing in cell F1 giving me the file names in that folder. I then use =IFERROR(LEFT(F7,FIND(".",F7)-1),"") in cell G7 giving the "actual" file name without the excel extension. This gives the dropdown listing the inventory listing. I COPY the selected item and paste this in Cell L3. I also have in M3 the excel file extension and in P1 the folder link. I then use in N3 =CONCATENATE(P1,L3,M3). giving H:\PROJECT-OPS\NSW Warehouse\NSWTA Inventory Listing\Budawang Inventory Listing.xlsx
I then use the following code to import the file selected
strMacro = ThisWorkbook.Names(strList) _
.RefersToRange.Rows(CLng(selectedIndex + 1)).Copy'.......COPY CELL SELECTED DROPDOWN
Sheet1.Range("L3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = False
sFolder = Sheet1.Range("N3")'..................SELECT THIS FILE TO USE THIS FILE FROM FOLDER
Set OpenBook = Application.Workbooks.Open(sFolder)
OpenBook.Sheets(1).Range("A1:J" & Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row).Copy ' ......COPY THIS FILE
ThisWorkbook.Worksheets("SiteFinder").Cells(Rows.count, "C").End(xlUp).Offset(3, 0).PasteSpecial xlPasteValues'....PASTE THIS COPY FILE AND PASTE IT IN THE FOLDER IN DATABASE
Range("A1").Select
Application.CutCopyMode = False
OpenBook.Close False
InsertTableArray1
Range("A1").Select
Application.ScreenUpdating = False
Might be a bit long but for the time it does work and I will get to your code laster today see how it works!.
Thanks
Steve
1 Guest(s)
