hi everyone, I'm trying to extract some values out of a file and into my excel 2007 workbook, using VBA and the dreaded Sendkeys function (copy-paste) after a shellexecuteEx call. Now, my macro is working fine when the file in question has already been loaded into the system memory. But if I'm running it with the file never having been ...
Hi, I'm using CDate to convert particular date formatted as string to excel Date type. I wrote a small UDF for this conversion, however when I run the function as a in-cell function, it returns a number and the calling cells default format is set to Number type. When I manually convert it to Date type, excel will display the correct da...
Hi - VBA newbie over here. I'm trying to use an array formula through excel vba but I can't seem to specify a dynamic range for the formula. I have: Range("xyz").FormulaArray = "=somefunction(Data!RC:R[8]C[49])" But next time it could be Range("xyz").FormulaArray = "=somefunction(Data!RC:R[15]C[32])" This doesn't seem to work. Is the...
I have a form (not a report) in Access displayed in Continuous View that has alot of repeat data. i.e. it looks like: State Names FL Abe FL Bart FL Charlie GA Deleanor GA Mary This needs to be interactive (there's some command buttons on each row as well as in the form header), but there's alot of repetitive d...
I noticed that Office 2010 comes with Visual Basic for Applications 7.0. However I can't seem to find much documentation on what changes were made. Does anyone have a summary of the changes, or any resources describing the differences? ...
I have a 3 dimensional array (5 x 5 x 3) and I need to post (5 x 5 x 1) to Sheet1, (5 x 5 x 2) to Sheet2, (5 x 5 x 3) to Sheet3. Because I am building this 3 dimensional array inside 3 nested for loops, I cannot use a for loop to access the (5 x 5) part of the loop. Is there any identifier that tells excel to index all elements of an a...
I'm working on a project that is basically a hack on top of a hack on top of an excel sheet and unfortunately we don't have time to re-factor but the core code simply isn't fast enough. We have assemblies which are made out of sub-assemblies and parts. So an assembly is the super structure and a part is the smallest structure. We have ...
Hi there, I have this: ActiveCell.Formula = "=COUNTIFS(G$4:G$" & (i - 1) & _ ", Sheets(ActiveSheet.Name).Cells(3, 25).Value ,F$4:F$" & (i - 1) & _ ",J4)" I want to use this: Sheets(ActiveSheet.Name).Cells(3, 25) so that I can make in loop to scan through column. as this reference cell must b...
I'm trying to use Excel VBA to write to a text file. I'm doing a few of these: MyFile1 = "C:\outputFromExcel1.txt" fnum1 = FreeFile() Open MyFile1 For Output As fnum1 and then writing to them like this: Print #fnum1, text All variables in the above are declared just with Dim. I'm writing hundreds of lines to the files and, very r...
I use the Powerpoint Object-Model to programmatically create a presentation in PowerPoint 2007. Some of the computers on which I run my program have PowerPoint set with Hebrew as the Primary Editing Language. (to change the primary language, push the "Office" button, then "Powerpoint options", and go to Popular->Language Settings->Prima...
I have an interview coming up for a VBA job. I didn't apply for it and was just asked to come in for an interview so I figured, "why not?" I havent used VBA in a couple years and while I'll do a little freshening up myself, I was wondering if anyone knew of any "gotcha" type VBA questions that might show up in an interview. ...
Hello, I am working on a table(in document A) which needs to be referred to another document(document B, not sheet 2 of document A). I used macro and VLOOKUP to link the data and it works perfectly well when both documents are opened. However, once i closed document B, the data on the table cannot be shown with a message " subscript out...
I have an xla file that refers to some other Excel add-ins that may or may not be present on the target machine. Currently Excel fails on loading the xla with a "Compile error: Can't find project or library". Code is something like: Dim result as Integer result = SomeAddIn.SomeFunction(x) Prefixing this with an "On Error Goto Add...
Hi, I want to find some styles and replace with some alternate styles. This has to be done in the whole document (including Header, Footer, Text boxes and body of the content) This has to be done in Word-VBA environment. Kindly provide me the code for the same. Thanks, Ram ...
I need a macro that will copy a column from one worksheet and paste into another spreadsheet on every 5th row. I've tried using snippets of code, but I haven't found one that I can modify to make this work. Driving me crazy. I'm sure this is pretty easy for those who are more advanced with VB code. Anyone willing to help me out would ...
is it possible to run a sub or function as soon as the user opens an access database file? if so, how? ...
HR manager asked me to pick a good HRMS system for them to use i have zero experience with HR systems. one of the factors that i would look at is how "open" it is and whether i can connect to it and run select statements on it, and whether i can program add-ons. can you recommend to me some HR systems that allow you to do these? the...
My task is to get text to print and display though it is longer than the 409 point row show. The sheet I'm working on is a Destination sheet from a Source sheet that can change often, but generally only 1 of 15 cells present this problem. Cell parameters are fixed so I can't change font or column width. On a spreadsheet I've made a Ma...
In .NET, I have developed a client library for my WCF service. I have registered the assembly for COM interop so that I can make calls to the library from VBA. However, since the executable using the library is not built in the .NET setting, library functions are unable to find the config file that defines the service bindings ('app.co...
Private Sub CmdPharmacy_Click() Dim myM(11) As String Set mydb = CurrentDb If PMNM = "" Then PMNM = UCase(Left(cboMonth.Value, 3)) End If sQ = "SELECT mN FROM PharDate WHERE mT = '" & PMNM & "';" Set myrec = mydb.OpenRecordset(sQ, dbOpenDynaset) myrec.MoveFirst myC = myrec.Fields("mN") For i = 0 To myC - 1 myM(i) = "M" & i +...