Dear all, as in my question title I am re-writing code of excel vba 2007 project to work with excel 2003, so instead of using connections in 2007 I will use queryTable in 2003, so my problem is if the connection string in 2007 is strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBF...
I want to dynamically add a radio button on a form, using VBA. I tried writing this code, but it crashes with 'Type Mismatch' Dim optionBtn As OptionButton Set optionBtn = UserForm1.Controls.Add("Forms.OptionButton.1", "name", True) optionBtn.Left = 10 optionBtn.Top = 10 optionBtn.Width = 30 optionBtn.Group = "q1" I also tried doin...
Hi, I have an excel sheet where a column has a list validation. When a particular entry from the list is selected, i need a message box to be popped up. I could have used the following code if there was only 1 cell, but in my case i have many cells in a column Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As R...
I have an Excel VBA macro which I need to run when accessing the file from a batch file, but not every time I open it (hence not using the open file event). Is there a way to run the macro from the command line or batch file? I'm not familiar with such a command. Assume a Windows NT environment. ...
I have the following function in VBA: Private Function Option1Checked(option1 As OptionButton) As Integer option1.ForeColor = vbGreen If (option1.Value = True) Then Option1Checked = 1 End If Option1Checked = 0 End Function Whenever I try to call the function like this counter = counter + Option1Checked(OptionB...
I have an application written in VBA for Excel that takes in a live data feed. Whenever there is a change in the data, various events are triggered within VBA. I also have some UserForms with ComboBoxes on them. My problem is that when I click the down arrow on the ComboBox and try to make a selection, the moment I get an update from th...
I've created a 3rd recordset (disconnected) from two existing recordsets that came from different connections. Now, I'd like to run a SQL query on this 3rd recordset. I am using Excel VBA. thanks, Harry ...
Hi guys... I am wanting to try something and I'm fairly sure it's possible, but not really sure!! In MS Excel (2003) can I write a VBA script which will open a location (eg: s://public/marketing/documents/) and list all the documents located within there (filename)? The ultimate goal would be to have the document name, date last modi...
I am using Excel to store a list of products, that will be then used to import into a web application. One of the attributes of a product is colour, which is to be selectable from a defined list of colours. The issue is that there can be multiple colours for a product. At present, I am using a dropdown list, which is on each row for a pr...
via Excel 2003 vba I made dsn connection by this method Sub CreateDataSourceFile() Set fso = CreateObject("Scripting.FileSystemObject") Set txtfile = fso.CreateTextFile("C:\program files\Common Files\ODBC\Data Sources\" & Me.cnnName & ".dsn", True) txtfile.WriteLine ("[ODBC]") txtfile.WriteLine ("DBQ=" & Me.DBFolderName)...
Hi folks, I am currently coding a form in excel. I was wondering if there was a way to search the contents of a field for certain characters. I was thinking about using this method to construct a code to check for the integrity of the data entered within the email field. It would look for an "@" and a "." and probably output a boolean v...
I have a column of numbers in Excel 2007, for example: 19 35 29 62 What i want to do is: If the value is between 0-20 replace the value with 0.1 If the value is between 20-25 replace it with 0.2 The bin sizes are not going to be the same (0-20, 20-25, 30-50, 50-60, 65+) etc, and the values to replace the numbers with are not necessar...
Hello gentlemen, I have a simple Excel speadsheet where in one column i write string values. What I need is to define some predefined values and instead of typing string select it from dropdown list. For instance for a column Store Name I want to select "Reebok", "Adidas" and so on. I understand that there can be several ways how to im...
I have an Excel add-in I am developing that works fine in Windows XP. However, I just got a hold of Windows 7 Professional and now I cannot save this add-in, either manually from the VBA Editor or programmatically using ThisWorkbook.Save. The error message says "Micrsosoft Office Excel cannot access the file 'C:\Program Files\Microsoft...
Sheet1 column B contains my customer number- I need to locate this customer number in Sheet2 column F. Then copy the value from Sheet2 AE (from the row with the matching customer number) into the matching customer number row in Sheet1 column E. Note that sheet1 column B and Sheet2 column F contain the same customer numbers, but not in ...
I have the below vba macro to Export the selected cells into a text file. The problem seems to be the delimiter. I need everything to be in an exact position. I have each column's width set to the correct width(9 for 9 like SSN) and I have the cells font as Courier New(9pt) in an Excel Sheet. When I run this it comes out REALLY c...
I'm using hssf api for reading data from xls files. But is there any way to get the exact no. of sheets in a workbook containing stored data? ...
I've been trying to pass a sheet in my workbook to a function, but it doesn't seem to be working. The function is Private Sub passToSheet(theData As Variant, Optional mySheet As Worksheet) I have tried doing Dim mySheet as Worksheet Set mySheet = "Results" then to call the function passToSheet theData mySheet but it doesn't seem...
I have some code which I found online and it's working great. It lists any files in a specified folder and their properties. I just need to amend this function which returns a list of files. I need it to look in a given path at ALL files. So if it's a folder, open it and return those files too. I eventually need to get this to mark each ...
I am trying to get a macro convert from VBA over to vb.net and I am getting a type mismatched error and can't figure it out. I am hoping someone here will be able to help me. This is the code. Sub SortRawData() Dim oSheet As Excel.Worksheet Dim oRange As Excel.Range Try oSheet = SetActiveSheet(mLocalDocument, "Sh...