I don’t really understand your overall approach to generate Excel sheet from the data in a database. Normally, I’d use Apache POI as proposed by Vivek. However, if you really need to call an Excel macro in a sheet, then you need two things: First, you need a JAVA-to-COM bridge like JACOB, COM4J or a … Read more
Here is a way to catch most outcomes of interacting with the dialog; Dim value As String value = InputBox(“Please enter a #”, “Determine Limit”, 10000) If (StrPtr(value) = 0) Then MsgBox “You pressed cancel or [X]” ElseIf (value = “”) Then MsgBox “You did not enter anything” ElseIf (Val(value) = 0 And value <> … Read more
Macros that take arguments are not visible in the macro box because there is no point in having them there. If they need arguments to run, they cannot be run from the macro box because there is no way to supply an argument to the macro in question. Normally, a macro shows up in the … Read more
Offset function dynamic range makes it possible. Sample data Steps Define a dynamic named range =OFFSET(Sheet1!$A$2,,,1,COUNTA(Sheet1!$A$2:$Z$2)) and give it a name mobileRange Right Click on Chart Click on Select Data This screen will come Click on Edit under Legend Entries.(mobiles is selected) change the Series value to point to mobileRange named range. Now if data … Read more
How can I hide and show all the standard Excel ribbon tabs using VBA (not XML) The answer is “YOU CAN’T“. AFAIK, you can’t do that using VBA. Unfortunately VBA doesn’t expose the tabs. The only options that you have are as shown in the image below So you can work with the commandbar, commandbarButton, … Read more
So I was kind of looking to get rid of Unfiltered Data rather than trying to reverse all the criteria and delete the visible cells I would use this one: Sub RemoveHiddenRows() Dim oRow As Range, rng As Range Dim myRows As Range With Sheets(“Sheet3”) Set myRows = Intersect(.Range(“A:A”).EntireRow, .UsedRange) If myRows Is Nothing Then … Read more
I will break this “Tutorial” in 3 steps 1) Writing your Excel Macro 2) Preparing your vbscript file 3) Setting the task in Windows Task Scheduler WRITING THE EXCEL MACRO Open a new File in Excel and in the module, paste this code Option Explicit Const strTo As String = “[email protected]” Const strCC As String … Read more
I did a little research and was able to write this code: strbody = “<BODY style=font-size:11pt;font-family:Calibri>Good Morning;<p>We have completed our main aliasing process for today. All assigned firms are complete. Please feel free to respond with any questions.<p>Thank you.</BODY>” apparently by setting the “font-size=11pt” instead of setting the font size <font size=5>, It allows you … Read more
Try to slightly modify your code: Sub forEachWs() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Call resizingColumns(ws) Next End Sub Sub resizingColumns(ws As Worksheet) With ws .Range(“A:A”).ColumnWidth = 20.14 .Range(“B:B”).ColumnWidth = 9.71 .Range(“C:C”).ColumnWidth = 35.86 .Range(“D:D”).ColumnWidth = 30.57 .Range(“E:E”).ColumnWidth = 23.57 .Range(“F:F”).ColumnWidth = 21.43 .Range(“G:G”).ColumnWidth = 18.43 .Range(“H:H”).ColumnWidth = 23.86 .Range(“i:I”).ColumnWidth = 27.43 … Read more
This is how I clear the data: Sub Macro3() With Sheet1.ListObjects(“Table1”) If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete End If End With End Sub