Is there any way to do a vba for each loop that loops a range but includes the empty cells/rows. PROBLEM: When I do a copy on the row from some other worksheet, the loop automatically skips the cells in the "A" column that are blank and skips that entire row completely and therefore I lose some data in that row because ONLY the A colum...
So I recently have been trying to incorporate more sub forms to make the UI more friendly. So I have a pre developed form that runs some VB, so for examples sake lets just say that it runs SQL statement, returns the recordset, and fills text boxes with the data (because this is sort of an interactive dashboard concept: dim db as databas...
I got the following simple functions: Public Function AddFields(field1 As Range, field2 As Range) As String AddFields = "=" + field1.Address + "+" + field2.Address End Function Private Function GetCustomerCount(monthNumber As Integer) As Range If monthNumber < 6 Then GetCustomerCount = Range("D13") ElseIf monthNumb...
I am trying to write an Outlook 2007 VBA script which will look at the currently opened mail item and be able to read the properties of and save off the digital signature of said mail item. Now I realize that Outlook prevents access to encrypt/sign a new email programatically, but here I am focused on messages which have already been re...
Hi I have to get the data in an Excel sheet into a Access database. The datastructures of the Excel sheet and the Access database are very different, so a lot of reformatting/restructuring has to be done. So I like to use VBA to import the data. I know that I can open the sheet in an Excel instance from VBA, then reading, converting and...
I am trying to set the data validation for a range of cells using VBA. I get a run-time error 1004 (so helpful) "Application defined or object defined error" with this code. With rngRangeToCheck.Cells(lrownum, 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Form...
some_integer = 97.45 * 1# what does this notation mean? what will some_integer = ? ...
I tried setting a few properties on this object to send an email with high importance, but nothing seemed to work. Here is what I tried: objEmail.Importance = 2 objEmail.Configuration.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") = "High" ' For Outlook 2003 objEmail.Configuration.Fields.Item("urn:schemas:mailheader:X-P...
I am trying to implement an onclick event for an anchor tag on a website from Excel VBA. The following code gives me error message. Set iePage = ieApp.document Set ieAnchor = iePage.getElementsByTagName("A").Item(5) ieAnchor.onclick = true The error message is a run time error that says 'Not Implemented' Can someone please advise on g...
I'm new to Access VBA development and being asked to debug and add features to an Access 2007 application that two previous developers worked on. A form displays records from a database and shows a button for each record. The button is supposed to open a file using the appropriate path. But when the user clicks the button, it always use...
I have a macro which updates a cluster column chart when values are entered in the range start and range end cells. I would like to edit this VBA code to instead create a 100% stacked column chart. How do I do this?? See example of current code below: Sheets("Inputs1").Select 'ActiveSheet.Inputs1.Select Range("A4").Select RangeStart ...
I am writing a routine which works out the accruals of certain benefits to people within a given month and the dates of payment of those accruals. There are a few things to loop through and I am wondering how to increase the efficiency of the loop structure (or use alternatives). When I take a certain benefit, I have to work out wheth...
hi my csv contain mix datatype f.ex. in cell E8 data format is date, and other data in this E column are string. i use this connetion string in classic asp ,VB Script and back end SQl server ChekCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path1 & ";Extended Properties=""text;HDR=YES;IMEX=1;FMT=Delimited""" Set rs1 = ChekC...
Hi all, I want my Access application to run an external program (in this case a R script) after the user clicks a button. I use this code: Dim RetVal RetVal = Shell("""C:\Program Files\R\R-2.10.1\bin\R.exe"" CMD BATCH --no-environ --silent --no-restore --no-save ""c:\test.R"" ""c:\test-result.txt""", vbHide) MsgBox RetVal This works ...
I have an XLA file that is to be deployed to a number of users in the organisation as an Excel add-in. My intention is to deploy it to a directory in the user's "documents and settings" folder in "Application Data\MyCompany". (In fact this is all working through a wrapper that copies the latest version of the XLA locally and installs it ...
I have a list of numbers, each group of numbers is separated from the other group by the number zero "0" or many zeros. I would like excel to get the maximum number of each group and post it in a cell automatically. ...
Lets say I have two tables, one for transactions, and another table who's primary key is the foreign key in the first table, and this relationship simply associates locations to transactions. I have a form with a list box that shows all the potential locations, so that the user can open some dashboard forms that only pertain to a given ...
I am trying to reference the HTMLSelect object in VBA. Assuming I have a form on the html page and a select control named LB. I can't figure out why I get an error message using the code below. Dim ieApp As InternetExplorer Dim ieSelect As HTMLSelect Dim iePage as HTMLDocument Set ieApp = New InternetExplorer ieApp.Visible = True ieAp...
when you close excel and you have edited your VBA code it does not prompt you to save. i just wrote 300 lines of code and it did not get saved. how do i force it to prompt when closing? ...
In VBA, the ListIndex property of a Combobox shows the index (starting at 0) of the item selected in the overall list (array) of values in the Combobox. It shows -1 if there is no selection made. When I bring up a sheet in Excel with a Combobox and the last value in it, it comes up with a ListIndex of -1, instead of the actual ListInde...