Use this one: Dim ws As Worksheet Dim range1 As Range, rng As Range ‘change Sheet1 to suit Set ws = ThisWorkbook.Worksheets(“Sheet1”) Set range1 = ws.Range(“A1:A5”) Set rng = ws.Range(“B1″) With rng.Validation .Delete ‘delete previous validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:=”='” & ws.Name & “‘!” & range1.Address End With Note that when you’re using Dim … Read more
You need to use an ADODB.Command object that you can add parameters to. Here’s basically what that looks like Sub adotest() Dim Cn As ADODB.Connection Dim Cm As ADODB.Command Dim Pm As ADODB.Parameter Dim Rs as ADODB.Recordset Set Cn = New ADODB.Connection Cn.Open “mystring” Set Cm = New ADODB.Command With Cm .ActiveConnection = Cn .CommandText … Read more
I would check the “End of File” flag: If temp_rst1.EOF Or temp_rst2.EOF Then MsgBox “null”
Use the .Clear method. Sheets(“Test”).Range(“A1:C3”).Clear MSDN documentation here.
I think this is what you want… Sub SaveWorksheetsAsCsv() Dim WS As Excel.Worksheet Dim SaveToDirectory As String Dim CurrentWorkbook As String Dim CurrentFormat As Long CurrentWorkbook = ThisWorkbook.FullName CurrentFormat = ThisWorkbook.FileFormat ‘ Store current details for the workbook SaveToDirectory = “H:\test\” For Each WS In Application.ActiveWorkbook.Worksheets WS.SaveAs SaveToDirectory & WS.Name, xlCSV Next Application.DisplayAlerts = False … Read more
I have never been in favor of Sendkeys. They are reliable in some case but not always. I have a soft corner for API’s though. What you want can be achieved, however you have to ensure that workbook for which you want to un-protect the VBA has to be opened in a separate Excel Instance. … Read more
Select allows selecting several objects at once. Objects that are selected are placed in the Selection object, which permits iteration. Selecting an object (say, a column) activates the object. Activating an object makes it the active object. Best way to think of it is “many cells can be selected, but only one may be the … Read more
I got tripped up by this little “feature” recently and wanted to raise awareness of some of the issues surrounding the IsDate function in VB and VBA. The Simple Case As you’d expect, IsDate returns True when passed a Date data type and False for all other data types except Strings. For Strings, IsDate returns … Read more
The reason can be inferred from the difference between NUL : No character. It is used for filling in time or filling space on the surface (such as surface of platter) of storage device where there are no data. We’ll use this character when we’ll be doing programming for data wipers (destructive and non-destructive both) … Read more
By referring this(http://www.excelforum.com/excel-programming-vba-macros/867665-application-cutcopymode-false.html) link the answer is as below: Application.CutCopyMode=False is seen in macro recorder-generated code when you do a copy/cut cells and paste . The macro recorder does the copy/cut and paste in separate statements and uses the clipboard as an intermediate buffer. I think Application.CutCopyMode = False clears the clipboard. Without that line … Read more