Ok well this might work for you, a function that takes a path and returns an array of file names in the folder. You could use an if statement to get just the excel files when looping through the array. Function listfiles(ByVal sPath As String) Dim vaArray As Variant Dim i As Integer Dim oFile … Read more
I think (from experimenting – MSDN is unhelpful here) that there is no direct way of doing this. Setting Criteria1 to an Array is equivalent to using the tick boxes in the dropdown – as you say it will only filter a list based on items that match one of those in the array. Interestingly, … Read more
Google is full of information on this. As Hans Passant said, Form controls are built in to Excel whereas ActiveX controls are loaded separately. Generally you’ll use Forms controls, they’re simpler. ActiveX controls allow for more flexible design and should be used when the job just can’t be done with a basic Forms control. Many … Read more
I just created 4 progress bars for you. Take your pick 🙂 The pie progressbar is based on Stephen Bullen’s PastePicture code. Rest of the progressbar are easy to create. I have attached a sample file in the end which you can download and test. FEW SNAPSHOTS CODE In the Userform Option Explicit Private Sub … Read more
Here you go, one complete method. Just send a DataTable and a file name and this does the rest. This snippet will also make the header row gray with bold text and will auto fit the columns. using OfficeOpenXml; using OfficeOpenXml.Style; public void ExportToExcel(DataTable dt, string FileName) { //create a new byte array byte[] bin; … Read more
You can create ActiveX objects like ScriptControl, which available on 32-bit Office versions via mshta x86 host on 64-bit VBA version, here is the example (put the code in a standard VBA project module): Option Explicit Sub Test() Dim oSC As Object Set oSC = CreateObjectx86(“ScriptControl”) ‘ create ActiveX via x86 mshta host Debug.Print TypeName(oSC) … Read more
In general, this is really a not a great question, as it fails the rules of StackOverflow, but still, this is some possible answer, producing this output: Public Sub TestMe() Dim myCell As Range Dim currentCell As Range: Set currentCell = Range(“D1”) Dim rangeToWrite As Range: Set rangeToWrite = Columns(“D:E”) Dim lastRow As Long: lastRow … Read more