Search Results

Search found 28163 results on 1127 pages for 'project vba'.

Page 23/1127 | < Previous Page | 19 20 21 22 23 24 25 26 27 28 29 30  | Next Page >

• VBA form_error vs on error

  - by dmr

  I am trying to set up error handling for a MS-Access application. The standard method to do this seems to be with an On Error statement in each subroutine/function. It seems simpler to me to use the Form_Error function to catch all the runtime errors in that form as opposed to an On Error statement for each sub/function called by an event on that form. (Obviously, for code in modules, there is no Form_Error function and therefore the only method is the On Error statement) What are the pros and cons of using On Error vs Form_Error?

  Read the article

• VBA-Excel return multidimensional array from a function

  - by alesdario

  I'm trying to write a function which returns a multidimensional array. The problem is that the size of the array isn't defined. My array is initialized in the function below my_list() Dim my_list() As String Public Sub Load_My_List() Dim last_column As Integer last_column = some_helper.Get_Last_Column(somw_worksheet) 'my array is resized in this point ReDim my_list(1 To last_column - 1, 1) Dim i As Integer i = 1 For index= 2 To ultima_colonna my_list(i, 0) = some_worksheet.Cells(2, index).value my_list(i, 1) = index i = i + 1 Next index End Sub So, how can i write a function which returns my_list ? Something like the function below generate a mismacthing type error Public function Get_My_List as String() Get_My_List = my_list End Function and how can i call this function properly? I think that something like Dim test() as String test = Get_My_List will doesn't work

  Read the article

• Execute VBA Macro via C# Interop?

  - by Jon Artus

  Hi all, just wondering if anyone could suggest why I might be getting an error? I'm currently trying to execute a macro in a workbook by calling the Application.Run method that the interop exposes. It's currently throwing the following COM Exception: {System.Runtime.InteropServices.COMException (0x800A03EC): Cannot run the macro Macro1'. The macro may not be available in this workbook or all macros may be disabled. I've put the containing workbook in a trusted location, set all of the security settings to their minimum values, and trusted programmatic access to my object model. I'm entirely out of ideas and Google's failed me so far! Has anyone done this, or can you suggest anything which I could try? Many thanks!

  Read the article

• Access VBA question: Change the query being referenced by a function, depending on context

  - by Tara Amatista

  I have a custom function in Access2007 that hinges on grabbing data out of a specific query. It opens Outlook, creates a new email and populates the fields with specific addresses and data taken from the query ("DecisionEmail"). Now I want to make a different query ("RequestEmail") and have it populate the email with that data. So all I have to do is change this line: Set MailList = db.OpenRecordset("DecisionEmail") and that's where I get stumped. This is my desired result: If the user is on Form_Decision and clicks the button "Send email", "DecisionEmail" will get plugged into the function and that data will appear in the email. If the user on Form_SendRequest and clicks the button "Send email", "RequestEmail" will instead get plugged in. The reason that these are different queries is because they contain very different information that is smudged about in different ways. However, since it's just one little thing that needs to change in the function code, I don't think a brand new function is a good idea. My last resort would be to make a brand new function and use the Conditions field in the Macro interface to choose between them, but I have a feeling there's a more elegant solution possible. I have a vague notion of setting the query names as variables and using an If statement but I just don't have the mental vocabulary for thinking through this.

  Read the article

• Excel pivot refresh, save and close in Access VBA code

  - by schneidm

  I am using the following code to refresh Excel pivot tables from an Access application. What is the best way to save and close the Excel app after the pivots refresh? In my last attempt the code was trying to save and close before the pivots had refreshed. Private Sub Command161_Click() Dim objXL As Object, x On Error Resume Next Set objXL = CreateObject("Excel.Application") With objXL.Application .Visible = True 'Open the Workbook .Workbooks.Open "myfilepath.xls" 'Refresh Pivots x = .ActiveWorkbook.RefreshAll End With Set objXL = Nothing End Sub

  Read the article

• Excel 2010 VBA code is stuck when UserForm is shown

  - by Denis

  I've created a UserForm as a progress indicator while a web query (using InternetExplorer object) runs in the background. The code gets triggered as shown below. The progress indicator form is called 'Progerss'. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = Range("B2").Row And Target.Column = Range("B2").Column Then Progress.Show vbModeless Range("A4:A65535").ClearContents GetWebData (Range("B2").Value) Progress.Hide End If End Sub What I see with this code is that the progress indicator form pops up when cell B2 changes. I also see that the range of cells in column A gets cleared which tells me that the vbModeless is doing what I want. But then, somewhere within the GetWebData() procedure, things get hung up. As soon as I manually destroy the progress indicator form, the GetWebData() routine finishes and I see the correct results. But if I leave the progress indicator visible, things just get stuck indefinitely. The code below shows what GetWebData() is doing. Private Sub GetWebData(ByVal Symbol As String) Dim IE As New InternetExplorer 'IE.Visible = True IE.navigate MyURL Do DoEvents Loop Until IE.readyState = READYSTATE_COMPLETE Dim Doc As HTMLDocument Set Doc = IE.document Dim Rows As IHTMLElementCollection Set Rows = Doc.getElementsByClassName("financialTable").Item(0).all.tags("tr") Dim r As Long r = 0 For Each Row In Rows Sheet1.Range("A4").Offset(r, 0).Value = Row.Children.Item(0).innerText r = r + 1 Next End Sub Any thoughts?

  Read the article

• what does rs.Fields(0) mean? (ADODB) VBA

  - by every_answer_gets_a_point

  dim rs As ADODB.Recordset ... ... ... capture_id = rs.Fields(0) what does .Fields(0) mean?

  Read the article

• Splitting a string with variable number of spaces VBA

  - by GVBA

  I have a file with a bunch of number in columns. These numbers are separated by variable number of spaces. I want to skip the first line and get all the other lines and separte each number on the line. Finally, I want to write each number on Excel. I've been able to get the lines and write them on Excel but I can't separate each number (I'm getting the whole line as one string). Does any body know how to split a string that has a variable number of spaces? Here is my code. Sub Test() r = 0 With New Scripting.FileSystemObject With .OpenTextFile("C:\Users\User\Desktop\File.tab", ForReading) If Not .AtEndOfStream Then .SkipLine Do Until .AtEndOfStream ActiveCell.Offset(r, 0) = Split(.ReadLine, vbCrLf) r = r + 1 Loop End With End With End Sub

  Read the article

• FTP a text file to a server using VBA in Excel

  - by Anindya

  I have an Excel worksheet where the user enters certain data, which I want to store in a text file and upload to a server using FTP. One site suggested adding a reference to "Microsoft Internet Transfer Control" and then define an "Inet" object to do the FTP. However, I am unable to find a reference with this name in "Tools - References" in the VB Editor. Does anyone know of a solution for this problem? Thanks in advance.

  Read the article

• Project Server 2010 site creation - the database connection string is not available

  - by Brandon Montgomery

  I am trying to get Project Server 2010 up and running on a Win Server 2008 box. I've got Sharepoint 2010 installed and Project Server 2010 installed. I open SharePoint Central Administration, then I go to Manage Service Applications Project Server Service Application. It looks like there is a site under the "Sharepoint - 80" section, but the Status says "Failed - see the Application event Log". When I click on the site and select "Retry" I get the same thing. In the event viewer, I see an error with SharePoint Foundation Search as the Source - it reads: Could not create a database session. Context: Application '276504a6-93b1-4c1f-a900-fd6ed9d5c117' Details: The database connection string is not available. (0xc0041228) How can I fix this? Is there some configuration I missed?

  Read the article

• Need a piece of advice about e-mail automation in ms exchange + ms office environment

  - by be here now

  Hi, guys. I need your help in the following simple situation. I've got an MS Exchange server and some client computers running on XP with Office 2003 installed. And I've got a process I need to automate. Twice a day a known list of people sends an e-mail to a certain mailbox (let's call it manager's mailbox) - basically, an accomplishment report. After recieving letters from all of these people the mailbox owner sends and e-mail to another mailbox, meaning that a certain process is done. What I need to do is to replace this manager's mailbox with a depersonalized mailbox that will accumulate all the reports and automatically send a message after collecting all of them. I am definitely not in a "oh my God, what shold I do?" situation, and currently my imagination shows me a couple of ways to solve this problem, which I'm going to try, and I'm not ascking for a ready solution. But since I'm not experienced in Office/VBA developement, I'd like to ask a corresponding pro's opinion. Can you point me to a right direction from the best practices' point of view?

  Read the article

• Grabbing "last saved by" metadata from access '03 file to excel '03 in VBA

  - by Spacious

  So I am in Excel trying to read the last saved metadata from an Access database, but I cannot figure out how on earth to do this. If I use environ$("username") it will give my the last saved information for the excel document where I am coding, not the access db that I want it to grab from. Excel has a built in function for grabbing username data, but I cannot find one for access. How can I do this?! Any help is greatly appreciated!

  Read the article

• VBA Variable from msgbox to report

  - by user1789785

  I have a macro which runs several Sql queries. One of these queries is run based off a date which in input in a msgbox within the macro (only companies after the date entered are generated). Is it possible to put the value entered in the msgbox at the time the macro is run in a table by itself? (my ultimate goal is to put the value on a report to indicate that values displayed are after the following date: variable) Thanks

  Read the article

• Excel VBA creating a new column with formula

  - by Amatya

  I have an excel file with a column which has date data. I want the user to input a date of their choosing and then I want to create a new column that lists the difference in days between the two dates. The Macro that I have is working but I have a few questions and I would like to make it better. Link to MWE small data file is here. The user input date was 9/30/2013, which I stored in H20 Macro: Sub Date_play() Dim x As Date Dim x2 As Date Dim y As Variant x = InputBox(Prompt:="Please enter the Folder Report Date. The following formats are acceptable: 4 1 2013 or April 1 2013 or 4/1/2013") x2 = Range("E2") y = DateDiff("D", x2, x) MsgBox y 'Used DateDiff above and it works but I don't know how to use it to fill a column or indeed a cell. Range("H20").FormulaR1C1 = x Range("H1").FormulaR1C1 = "Diff" Range("H2").Formula = "=DATEDIF(E2,$H$20,""D"")" Range("H2").AutoFill Destination:=Range("H2:H17") Range("H2:H17").Select End Sub Now, could I have done this without storing the user input date in a particular cell? I would've preferred to use the variable "x" in the formula but it wasn't working for me. I had to store the user input in H20 and then use $H$20. What's the difference between the function Datedif and the procedure DateDiff? I am able to use the procedure DateDiff in my macro but I don't know how to use it to fill out my column. Is one method better than the other? Is there a better way to add columns to the existing sheet, where the columns include some calculations involving existing data on the sheet and some user inputs? There are tons of more complicated calculations I want to do next. Thanks

  Read the article

• vba word 2007 - protect document but enable toolbars

  - by laetitas

  I have a document with sections that are protected and unprotected. With Word 2003 the sections that were unprotected had formatting enabled - specifically the bullets and numbering. Now with Word 2007, even though the same sections are unprotected, the bulleting and numbering is disabled. Is there anyway to enable this? I tried playing around with CommandBars but nothing has worked. e.g. Application.CommandBars("Formatting").Enabled = True

  Read the article

• How to get the numbers matching with the name in vba code

  - by gorPweN

  Sorry about that. Basecly I want all the numbers in Col A That match with the names in Col B. I want to fill Col C with those numbers by order. Thank you

  Read the article

• Textmate: Find and replace across project with contents of one file from said project

  - by griotspeak

  I have a regular expression to find the text I want (I wrapped the relevant section in custom tags), and I can do it by hand without much issue, but what I want is a way to automatically find and replace throughout the entire project. A macro seems like an OK idea, but it would be nice to have a command (to edit and tweak). sed seems like a good bet, but I am pretty unfamiliar with it. I am not so much asking for a complete solution as I am asking for an example that does something close to what I want. I don't really know of a good way to start.

  Read the article

• Excel VBA: can delete validation but not add new one

  - by user1882965

  My code is as follows If Cells(Target.Row, 2) = "" And (Cells(Target.Row, 3) = "" Or Cells(Target.Row, 3) = "") Then Sheets("MySheet").Activate Cells(Target.Row, 3).Activate ActiveCell.Validation.Delete If (Cells(Target.Row, 2) = "Type A") Then ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=AvailableVersions" ElseIf (Cells(Target.Row, 2) = "Type B") Then ActiveCell.Validation.Delete Else ActiveCell.Validation.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertInformation, Formula1:="0", Formula2:="9999999" End If End If So the issue I am having comes whenever I reach ActiveCell.Validation.Add Run Time Error '1004': Application-defined or object-defined error Not a very helpful error, and also this occurs with both number and list validation type, so I am sure it is not an issue with the list itself which has workbook level scope anyway. It never occurs on ActiveCell.Validation.Delete which I find weird? I have been all over google trying to find a solution, and most suggest that it is caused by running dynamic validation code from a button which hogs focus despite the Activate call, but I am running on sheet change event rather than on button press so I don't think this is my issue - any ideas? I've wasted basically a whole day on this! :(

  Read the article

• Generate digital signature using VBA

  - by tputkonen

  I have text cells of arbitrary length in Excel's column A. Using a private key, I want to generate digital signature for data in column A into the cell in column B. I'm looking for a macro implementation for the digital signature algorithm - I don't think one ships with Excel, but is there an addon available? It must be possible to easily verify correctness of the signature in other environments as well.

  Read the article

• Repeating Random Variables VBA

  - by Soo

  How can I use randomize and rnd to get a repeating list of random variables.

  Read the article

• VBA Range.Rows.Count giving unexpected results

  - by Jeffrey

  I have a Range variable that contains an address - $2:$2,$4:$205,$214:$214 - (3 groups of rows). I would like to get the count of all the rows in that range. However, range.Count gives me the count of all the cells (50,000~) and range.Rows.Count only return 1 - the count of all the rows in the first group. How do I get the count of all the rows Thanks

  Read the article

• need help me in excel-vba

  - by aos

  Private Sub cmdClear_Click() Dim Confirm As Integer Confirm = MsgBox("Are you sure you want clear this Sheet?", vbYesNo, "WARNING: Date Changed") If Confirm = 6 Then Sheets("OPV").Activate 'Sheets("OPV").Activate Sheets("OPV").Range("B4:BZ1000").ClearContents Sheets("OPV").Range("B4:BZ1000").Interior.Pattern = xlNone Sheets("OPV").Activate Sheets("OPV").Range("B4").Activate MsgBox " Done .. ", vbInformation, "Clear ......" End If End Sub

  Read the article

• Separate "Year" to several worksheets according to one column....

  - by HACHI

  hello! This task is driving me mad... please help! Instead of manually type in the data, i have used VBA to find the year range, put into one column and delete all duplicate ones. But since excel could give more than 20 years, it would be tedious to do all the filtering manually. AND, now i need excel to separate the rows that contain the specific year range in any one the three columns and put them into a new sheet. e.g. The years that excel could find in the three columns(F:H) are ( 2001,2003,2006,2010, 2012,2020.....2033).. and they are pasted in column "S" in sheet 1. How could i tell excel create new sheets for the years ( sheets 2001, sheets 2003, sheet2006....),search through column (F:H) in sheet 1 to see if ANY of those columns contain that year, and paste them into the new sheet. To be more specific, in the newly created "Sheet 2001", the entire row where column(F:H) contains "2001" should be pasted. and in the newly created "Sheet 2033", the entire row where column(F:H) contains "2033" should be pasted.. Enclosed please find the reference. http://www.speedyshare.com/files/23851477/Book32.xls I have got sheet "2002" and "2003" here as results but for the real one i will need more years' sheets (as many as how many excel could extract in the previous stage; as shown in column L ) ...... I think this task should be quite usual (extracting by date), but i couldn't google the result....Pleas help!!I am very clueless about how to do LOOPING.. so please advice and give in more details! Thanks

  Read the article

• How to select the range for pasting using vba

  - by user1616384

  I wrote some code for selecting the particular row and pasting it in column wise using paste-special property. It is working correctly my code is : lngRow = Me.TextBox4.Value strCol = Me.TextBox5.Value Set rng = Range("A:A").Find(What:=lngRow, LookIn:=xlValues, LookAt:=xlWhole) If rng Is Nothing Then MsgBox "Value not found in row 1", vbExclamation Else Range(rng, rng.End(xlToRight)).Copy Range("A1:E3").Columns(strCol).Offset(, 1).PasteSpecial Transpose:=True Range("A1:E3").Rows(1).Copy Range("A1:E3").Columns(strCol).PasteSpecial Transpose:=True endif the problem here is I am using Range(rng, rng.End(xlToRight)).Copy to copy the values and for pasting I am using Range("A1:E3").Columns(strCol).Offset(, 1).PasteSpecial Transpose:=True. How can I paste all the values which are copied? Because if the values are in column F then this macro will not paste those values.

  Read the article

• Navigate a website with Excell Vba

  - by Ommit

  I can pull data from known ulr, but can I navigate a website with excel. For example can excel do a google search and put the results on a spreadsheet. Or navigate an in-frame site. This code pulls data from a website. With ActiveSheet.QueryTables.Add(Connection:= _ PUT_URL_HERE, _ Destination:=Range("A1")) .Name = "I need serious help" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With

  Read the article

< Previous Page | 19 20 21 22 23 24 25 26 27 28 29 30  | Next Page >