I am filtering a DAO recordset for sub-results as part of a set of recursive tasks. I'm trying to speed the routine, and I can see the recordset is being opened fresh every time the class object is instantiated. This step happens many hundreds of times. Isn't there a way to re-use it? The keyword here is persistence, isn't it? I'v...
I have a tab-delimited text file and want to import it in MS Access using VBA code. I have created an MS Access form and have used the DoCmd.TransferText method: DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage) It works well for CSV File. I'm not sure how to do it in cas...
I'm trying to get a handle to the worksheet that was created by a copy operation. following code used to work: Dim wsTempl As Worksheet, pageCount as Long Set wsTempl = Sheets("Template") For pageCount = 1 To 5 wsTempl.Copy After:=Sheets(Sheets.Count) Set ws = Sheets(Sheets.Count) ws.Name = "p" & pageCount Next But stopped w...
I have this macro that is password protected and shows a form. Recently I added code to import data from excel and every time I close it asks me for a password - I can cancel through it but I'd like to make it go away, I see no plausible reason why it would come. I've separated out the few lines that causes this problem Sub a() U...
Rookie question I know. I have a table with about 10 fields, one of the fields is a category field. I need this field to exist because of the multiple types of categories. However, one category in this field is wrong and is duplicating results. So can I delete all records in the table that have "Type320" in the CatDescription field, an...
I have an array constant defined in cell A1 as {1,2,3}. This displays as "1" (the first value in the array). I would like to have the formula SUM(A1) return 6. However, SUM is using A1 as a single-celled array, rather than the array constant contained inside A1 - and therefore SUM(A1) returns 1. Likewise, I would expect AVERAGE(A1)...
I have written a macro in excel 2007 to log water-level readings. Once logged, it automatically charts the data for each of the 30 wells. However, when the workbook is opened in Excel 2003, the chart does not work complaining that the chart data is too complex to be displayed (works fine in 2007). There is one series per well (each well...
Hi, I have a spreadsheet that has a colour key at the top. Users are expected to manually set the colour of various cells in the spreadsheet in line with the colour key. Ideally what I'd like is for users to be able to highlight/select one or more cells in the spreadsheet they wish to give a colour to, then with one click on the colou...
Using Excel 2007, is it possible to change the transparency of chart grid lines using VBA or VB.NET? I have the following code, which throws an exception on the last line: Dim axis As Excel.Axis = chart.Axes(Excel.XlAxisType.xlCategory) axis.HasMajorGridlines = True axis.MajorGridlines.Border.Color = Color.Gray.ToArgb axis.MajorGridlin...
I use "WinHttp.WinHttpRequest.5.1" to send HTTP POST requests from VBA in Excel. But I could not manage to do it for HTTPS, as I received an SSL certificate error. What VBA code would you use to negotiate an SSL connection to a website from VBA in Excel ? ...
Hi, I am currently testing how to send an e-mail automatically using VBA within Outlook 2003, to start I have copied the below from the help files: Sub CreateHTMLMail() 'Creates a new e-mail item and modifies its properties' Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = Outlook.Application ...
Hello! I am currently writing a vba macro to send e-mails and the messages are created but do not sent as an error is generated. My current code is: Function CreateHURMail(Filename) Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Set olApp = New Outlook.Application Set objMail = olApp.CreateItem(olMailItem) With obj...
Is there anyway to determine the form name that the report was called from? I would like to write some VBA code in report to determine where is the report was called from. I have Access 2003. ...
I have an Access form displaying a tiny amount of data for a certain type of record. Basically it just prints the name on the left and on the right has a bunch of Rectangle controls whose background color I change in the form's OnLoad() function, according to results from a query performed using that record's ID as parameter. This all w...
Suppose, for instance, that I want a method that adds a ComboBox. Maybe I try this Public Sub AddComboBox() Dim cb As MSForms.ComboBox Set cb = <Calling form module>.Controls.Add("Forms.ComboBox.1") End Sub How can I get <Calling form module>? ...
I'm working on a protected Excel spreadsheet and ran into a problem with cells that I've programmatically locked that are still selectable. I can't click on them directly to select them, but if I select the cell to the lower right of a locked cell and then shift-click the one to the upper left, all 9 cells (including the 'locked' one) a...
I'm trying to open a series of Excel spreadsheets using an instance of Excel created inside of a module in an Access database. I can get the files to open properly; however, the actual call to make Excel start takes quite a while, and to open the files takes even longer. The location of the files doesn't matter (same time to open on a ...
I need to combine rows from two Excel worksheets into one long row, based on an ID column in each of the worksheets. Effectively I want to perform the equivilent of a SQL JOIN on two worksheets to produce a combined worksheet. I can get a Range object for a row in the first worksheet, and then use the ID column to get a Range object for...
Hello, My boss asked me to export the corp's Mantis bugs database to Excel, but he can't give me access to the SQL Server, and the process has to be automated. The ONLY thing I can use is Excel (no ODBC, no manual export). So I managed to do this : Dim webClient As Object Dim i As Long, vFF As Long, oResp() As Byte Dim vLocalFile As...
Right now I am working on a new database that will show changes to one of our manuals that people request. I have two tables and one form. The first table is were all the information will be stored from the reference or subchapter of the manual, the current text in that subchapter, and what the proposed changes are. The second table i...