vba

How to summarize date counts

I have 3 Worksheets. Following is an explanation of what I am trying to do. EM11 EM12 EM01 The steps below are meant for Sheet EM11 but I want to repeat them FOR Sheet EM12 and Sheet EM01 Select Sheet EM11 Copy J2:J65636 and copy G2: G65636 Create a new worksheet called EM11-Count Paste Column J into cell A2 of new worksheet ca...

run time error '9' Subscript out of range

hello, i am trying to run a solver optimisation through vba 6 excel. but when the execution reaches the declaration of the objective function: Sheets(working).Range("B63").FormulaArray = _ "=MMULT(MMULT(decision,covarMatrix),TRANSPOSE(decision))" it gives the following message "run time error '9' Subscript out of range" ...

"50289 Can't perform operation since the project is protected" but why?

I am supplying the correct password Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("H:\M\X\C.xls", 0, , , "password") 'any of these lines cause the error mentioned Set vbcomp = objWorkbook.VBProject.VBComponents(modname) objWorkbook.VBProject.VBComponents.Remove vbcomp objWorkbook.VBProject...

Pivot Chart Creation Using Access 2007 VBA

OK there are absolutely no good articles on the internet that I can find that explain or have code examples on how to create a pivot chart using VBA. I need this because I need the pivot chart to show different results depending on user selection in a form. There are some for Excel but the syntax is different for Access. I know this is a...

How to Replace Multiple Characters in Access SQL?

I'm a novice at SQL, so hopefully someone can spell this out for me. I tried following the "Replace Multiple Strings in SQL Query" posting, but I got stuck. I'm trying to do the same thing as the originator of the above posting but with a different table and different fields. Let's say that the following field "ShiptoPlant" in table "...

How to execute a literal in VBA?

Is there a way to execute a literal such as, UseValueKey = ExecuteMethod("Date()") I want to have the variable UseValueKey return the actual date. I am using VBA. Any ideas? ...

VBA - Parse Email Text to Access 2000 Class Instance

I am now maintaining a legacy VBA/Access 2000 application for a client. They have a customer who emails orders with text that looks like this Contact: Peggy Hill Company: Arlen Residential Mortgage Finance Co Address: 43456 South 18939 West, Suite 47995 City: Arlen City ContactState: TX ContactZip: 88888 Phone: 8019990000 Email: peggy....

Unlock cell on a condition from adjacent cell

I have two columns but the codition I would like is to be evaluated from one cell to another. The first column has cells which have a drop down validation with names, and the second will activate only if a certain name from the adjacent cell is selected. so far i only found this code but it does not seem to work: Private Sub Worksheet...

Update an Access Link Table when the underlying SQL Server table's columns change

At work we've got a SQL Server database that several users connect to (read only) using Access 2003. This was fine, except now one of the tables they look at is re-created fairly often and part of this process involves a cross-tab query. Which means that, depending on the data, the number and and names of the columns potentially change...

How do I delete a command button using Word VBA?

I have a Word document that contains a command button named "update". How can I delete this button using VBA? thanks, Paul ...

Create a custom worksheet function in Excel VBA

I have a faint memory of being able to use VBA functions to calculate values in Excel, like this (as the cell formula): =MyCustomFunction(A3) Can this be done? EDIT: This is my VBA function signature: Public Function MyCustomFunction(str As String) As String The function sits in the ThisWorkbook module. If I try to use it in the ...

Using VBA to parse text in an MS Word document

Hi, I was hoping someone could help with a MS Word Macro. Basically, I have a MS Word document which lists out several text files and specific pages of interest in each file. The file format is similar to: textdocument1.txt P. 6, 12 - issue1 textdocument2.txt P. 5 - issue1 P. ...

Use MS Word Macro to search for a text file

Thanks for the help on my previous question. With the suggestion I was able to read in the line of text from the Word document. I was then able to proceed to parse the line to pull out the file name. I am now trying to initiate a file search using that file name. Does anyone have any suggestions on how to use MS Word Macro to initiate ...

Microsoft Access string parsing

In MS Access 2003, I have the following values in a column 0-0-60 20-0-0-24S 20.5-0-0-24S 32-0-0 How can I write my query such that I can pull the 3rd item from the column, example the "60" from "0-0-60". Keep in mind that I've optionally got the 4th column also. ...

Excel Doesn't Auto-Fit 45º Text

When I orient column headers at 45º I have to manually resize each column since Auto-Fit won't let the oriented text overlap with the neighboring cell. Is there a way to programatically (with VBA) auto-fit the columns where they'll overlap? I'd like a solution which takes font size into account too. ...

Textbox in custom toolbar.

Is it possible to put textbox control in custom toolbar in Excel. I have created an Add-in that shows this toolbar. What I want to do is when user types in textbox Add-in should call a procedure or function depending what user has typed. I would like to do it in VBA in MS Excel. Thanks. ...

VBA & PowerPoint

How do I rename a slide in VBA? Need help on a sample scenario: I have a slide and have a command button on it. When clicked, it should pop up a window/ image and when clicked again should dispose the window. We can do it. When copied into another deck, I cannot use the default slide name from deck 1. how can we make a slide with VBA ...

Storing arbitrary meta-data in Microsoft Word document

I need to store custom meta-data in a Word document. The 'document properties' are limited to 255 bytes but I have data which is >> 10k We are using VBA to write a word extension to interact with our application and want to have our application data stored in the word document. The idea is that that the user can share just the word docu...

How to append to a word document with VBA Macros

Hi, I am writing a macro in MS word. I need the macro to parse through a list of filenames, page numbersm and notes and filter out only the file names and page numbers. Each paragraph (line) in the document refers to a different file, so I am looping through For/Next statement. For each new line, I'm pulling out the filename, and pagenu...

How do I clear a table in Access with VBA?

What I'm trying to do is, while in Excel, use VBA to push data to an exsisting Access table. I've been able to do this, but am having one small hickup. Before I push the data to access, I want to clear the current data on the Access table, so when the new data from Excel comes in, it is the only data in the Access table. I really don'...