Copy Paste Values only( xlPasteValues )

If you are wanting to just copy the whole column, you can simplify the code a lot by doing something like this: Sub CopyCol() Sheets(“Sheet1”).Columns(1).Copy Sheets(“Sheet2”).Columns(2).PasteSpecial xlPasteValues End Sub Or Sub CopyCol() Sheets(“Sheet1”).Columns(“A”).Copy Sheets(“Sheet2”).Columns(“B”).PasteSpecial xlPasteValues End Sub Or if you want to keep the loop Public Sub CopyrangeA() Dim firstrowDB As Long, lastrow As Long …

Read more

VBA + Excel + Try Catch

Private Sub Workbook_Open() on error goto Oops version = “1.0” Set objHTTP = CreateObject(“WinHttp.WinHttpRequest.5.1”) URL = “<WEB SERVICE>” objHTTP.Open “POST”, URL, False objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)” objHTTP.setRequestHeader “Content-type”, “application/x-www-form-urlencoded” objHTTP.send (“version=” + version) exit sub Oops: ‘handle error here End Sub If you wanted to, for example, change the URL …

Read more

How to detect if user select cancel InputBox VBA Excel

If the user clicks Cancel, a zero-length string is returned. You can’t differentiate this from entering an empty string. You can however make your own custom InputBox class… EDIT to properly differentiate between empty string and cancel, according to this answer. Your example Private Sub test() Dim result As String result = InputBox(“Enter Date MM/DD/YYY”, …

Read more

How to get a list of installed OLE DB providers?

If you have powershell available, just paste this into a powershell command prompt: foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator()) { $v = New-Object PSObject for ($i = 0; $i -lt $provider.FieldCount; $i++) { Add-Member -in $v NoteProperty $provider.GetName($i) $provider.GetValue($i) } $v } Credits and more advanced usage: http://dbadailystuff.com/list-all-ole-db-providers-in-powershell

Change the color of cells in one column when they don’t match cells in another column

Select your range from cell A (or the whole columns by first selecting column A). Make sure that the ‘lighter coloured’ cell is A1 then go to conditional formatting, new rule: Put the following formula and the choice of your formatting (notice that the ‘lighter coloured’ cell comes into play here, because it is being …

Read more

Wait for shell command to complete [duplicate]

Use the WScript.Shell instead, because it has a waitOnReturn option: Dim wsh As Object Set wsh = VBA.CreateObject(“WScript.Shell”) Dim waitOnReturn As Boolean: waitOnReturn = True Dim windowStyle As Integer: windowStyle = 1 wsh.Run “C:\folder\runbat.bat”, windowStyle, waitOnReturn (Idea copied from Wait for Shell to finish, then format cells – synchronously execute a command)