What can you do with Excel vba you cannot with c#
I ve heard things like you cannot manipulate tables in C# but can through VBA. Does any one know what can be done via VBA which cannot be done via C# PIA? ...
excel-vba
Excel 2007 VBA Find row based on date
Date | data | data | data 12/29| G | F | G 12/30| G | | I have a spreadsheet like above. I want to find the row that is the current date, then reference the row that is the current date in a Range type. Then cycle through the data in that row. I can find the current date, and get the address of the cell that is the cu...
Can you find the row number for a particular value with macro in VBA excel ?
Hi, I'm trying to find a command which returns the index of the row for which a particular value occurs. In addition, the value cannot be empty. In total, there are 3 worksheets. Worksheet A has a column with all the values. Worksheet B has a column with values which appear in worksheet A and columns with more information for that val...
how do I express if value is not empty in VBA language ?
Hi, how do I express the condition "if value is not empty " in VBA language ? Is it something like this ? "if value is not empty then.." Edit/Delete Message ...
Excel VBA: Secure way to get user's database login/password for connection string?
I have an Excel VBA tool which needs to query Oracle. I am using ADO. When building the connection string, I get the database, username, and password out of cells in the spreadsheet. What is a more secure way to handle the login? The spreadsheet will probably be emailed and saved on public drives. Assume an ordinary Office Professional ...
How can I find the index of a row for a value in excel É
Hi , is there a function in excel which can find the index of the row for a particular value É ie if I write function(5.77) it will return 5 , because 5.77 appears in row 5 . '1 Frequency '2 4.14 '3 4.19 '4 5.17 '5 5.77 '6 6.39 ...
How can I copy columns from one sheet to another with VBA in Excel?
Hi, I'm trying to write a macro that copies the content of column 1 from sheet 1 to column 2 on sheet 2. This is how the module looks like but, when I run it, I get run time error 9, Subscript out of range. Sub OneCell() Sheets("Sheet1").Select 'select column 1 A1' Range("A1:A3").Select Selection.Copy Range("B1:B3"...
MS Excel 2003: Partially Refresh PivotCache or Combine PivotCache
I have an Excel pivotcache that is connected to an Access database query. However, if I change the source data in Access (e.g. change a value, add / delete a record), I have to refresh the pivotcache which in turn runs the query again to retrieve all records on the entire dataset. This is very inefficient. I only want to retrieve records...
how do I deal with error 1004 in vba excel ?
Hi, Basically I have 3 worksheets . From the first worksheet I get a value (rotid) , and by using the match function I try to find the value in the second worksheet , and copy the row associated with the value into worksheet 3 where y is the index of the row in sheet 2 which corresponds to the value (rotid). But sometimes a value is...
What happens does resume next at the end of the code stands for in vba ?
Hi, Can somebody explain me what this code means ? I guess its if an error occurs go to section ErrHandler, but why is there a resume next at the end and Exit sub inbetween ? On Error Goto ErrHandler: N = 1 / 0 ' cause an error ' ' more code ' Exit Sub ErrHandler: ' error handling code Resume Next End Sub I also wrote a short co...
How to query Microsoft Access Database fields from VBA in Excel
Hey guys, I am currently writing a program to save stuff entered in a form in Excel into a database in Access. I am trying to increment a primary key field within Access by "1" whenever I add data from the fields in my Excel form. Because I have declared this field as a PRIMARY KEY NOT NULL field, it doesn't allow me add another row o...
Copy Text from Range in Excel into Word Document
hi, how do you: 1) copy text from a range in an Excel Document. 2) Open a Word Document. 3) inserts the text into a specific part of the word document. regards Kojo Edit: here is the approach Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Dim j As Integer Set wrdApp = CreateObject("Word.Application") wrdApp.Visi...
Excel commands not available when macro is paused
In Excel 2003, I was able to pause a macro switch to Excel and perform excel functions, then switch back to VB and resume the macro. In Excel 2007 when I pause a macro and switch to excel, most of the excel functionality does not respond. I can switch between sheets and perform 'browse' type functions, but I cannot change cell values or...
How can I use VBA's SUMIFS properly?
Hello, I'm trying to write in Visual Basic (in Excel 2007) the following sumifs code (the I column and the F column are contained in a Table): Range("AA5").Value = "=SUMIFS(I5:I420,K5:K420," & "B1" & ",F5:F420," & "6009" & ")" "B1" and "6009" is what I want it to look for and the I column is the column I want added. Can you plea...
How to find the X and Y co-ordinates of points on an Excel scatter plot
I've got a scatter plot which I generate and set the data for programatically. The only problem is that if the data series are close together the labels find themselves right on top of each other. I have an algorithm in mind for moving the labels around so they remain close to their corresponding data series and are positioned so that t...
How to read the value of a drop down list on a sheet in Excel using VBA
As per the title, i have a drop down list which is already populated. On a button command i want to read the selected value from this drop down list which i will use to drive an ADO query. I did VBA in Access years ago so have some idea of what i am doing but using it in Excel is new to me. As a side question can anybody point me in ...
Excel 2003 doesn't have WorkbookConnection class
How to get over this problem Excel 2003 does not have WorkbookConnection class while 2007 have it. ...
Convert VB's Format() into PHP
In PHP, I'm trying to read an Excel file using COM(): $rrc_app = new COM("Excel.application"); $rrc_workbook = $rrc_app->Workbooks->Open($filename); $rrc_worksheet = $rrc_workbook->Worksheets('Top sheet'); $rrc_worksheet->activate; I tried to extract the value of a cell containing a "Time" value: $review_time = $rrc_worksheet->Range(...
Convert vba to vb6 and create a .dll - how to - hints, tipps and risks
Hi, I should convert a huge load of code which was written in VBA (Excel) to VB6. But I really do not know what I have to take care of or where to begin. Therefore it would be great to get some hints here from the VB6 experts. I installed already MS Visual Studio and played a bit around. But I'm not a VB6 expert and do not really know ...
Excel: Runtime error 13 type mismatch
I got Run-time error '13' at then end of this code, even if I try to switch between the commented lines of code Public Property Get cnnString() As String 'old: strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:S...