Help Deploying VBA changes across hundreds of Word Temlates

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Why does (VBA Excel) Solver becomes slower when executed in a for next loop more than 1000X? Problem: Solving problems becomes slower the more often I call the SolverOK function in a VBA Excel 2002 code. I am using the solver add in my program and I was wondering if that will use up a lot of memory and I want to make sure that I release the memory properly. The solver is called several 1000 times in this routine and it could possibly produce a big mess if it does release the memory correctly. Any help is appreciated. Code example 'Open solver library Application.Run "Solver.xla!Auto_Open" 'Clear solver contraints SOLVERReset For i=1 to 100 'Set solver contraints SolverAdd CellRef:="$H$114", Relation:=1, FormulaText:="$H$103" SolverAdd CellRef:="$H$119", Relation:=3, FormulaText:="$H$103" 'Call solver and calculate result SolverOk SetCell:="$H$101", MaxMinVal:=2, ValueOf:="0", ByChange:="$H$103" SolverSolve UserFinish:=True SolverFinish KeepFinal:=1 'reset solver after the job is done SOLVERReset NEXT ' close solver library Application.Run "Solver.xla!Auto_Close" Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 2
    - Macro To Delete Spreadsheet Columns if no data in column Hi, I need a routine that will go across and delete any columns in a spreadsheet that don't contain any data. The spreadsheet will always be from columns A:BB. There are column headings on row 1 going across. Thanks for your help - I very much appreciate this forum. It has helped me out greatly! Gary Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 3
    - What is the VBA code to insert an Object into an Excel File Hi, I have recently been requested to create a button on an Excel spreadsheet that will insert an Object (usually a MS Word Document but not always) into the Spreadsheet as as Object. The user would need to browse for the document and select the file, then the file would attach and be displayed as an icon. In short, the smae as using the Insert > Object >Create From File (Display as Icon) functionalily that is available from the Standard Toolbar. My VBA skills are extremely limited and I would appreciate any assistance that I can get on this one. Thanx in advance, Amanda Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 4
    - Excel VBA open/select text in Word? I'm trying to use Excel VBA and open a word document and select a few fields of a table to paste into Excel. Is there a way to search for a certain string and copy the adjacent table fields in Word so I can assign them to a variable for pasting into Excel Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 5
    - Read data from excel through VB.NET Hi friends, I need to read symbols(like ALPHA,BETA,MEU) from excel and show it in a data grid through VB.NET.Later insert it into Oracle 8i DB. I am able to read entire data but i am getting 'a' instead of ALPHA symbol and 'b' instead of BETA symbol. I am querying excel via OLEDB provider.The following snippet is using for accessing excel. Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Dim MyConnection As System.Data.OleDb.OleDbConnection MyConnection = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=" & ExcelFilePath & "; " & _ "Extended Properties=Excel 8.0") ' Select the data from Sheet1 ([in-house$]) of the workbook. MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [in-house$]", MyConnection) DS = New System.Data.DataSet MyCommand.Fill(DS) Dt = DS.Tables(0) DataGrid1.DataSource = Dt Please help me to resolve it Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 6
    - Vba - Directories / Folder tree Hi, Is there any way to show a directory / folder tree In order to move a file from "myFolder01" to "myFolder02" I need to let the user to select the folders myFolder01 and myFolder02. Thanks in advance, Aldo. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 7
    - Null Value Error in recordset I have encountered a problem when returning a record set which contains a Null value. Please can any one help I am trying to determine if a date field against a client is empty but if it is empty, when I try to get the value, I get an error message when it gets to dteDateCreated - "Invalid Use Of Null" Dim SQLText As String Dim rs As Recordset Dim strClientCode Dim dteDateCreated As String Dim strCode As String strClientCode = [Forms]![frmClient].[ClientCode] SQLText = "SELECT tblClient.ClientCode, tblClient.DateCreated " & vbCrLf & _ "FROM tblClient " & vbCrLf & _ "WHERE (tblClient.ClientCode) = " & strClientCode & ";" Set rs = CurrentDb.OpenRecordset(SQLText) strCode = rs.Fields("ClientCode").Value dteDateCreated = rs.Fields("DateCreated").Value It works OK if there is a date present. I am using Access 2000 on Windows XP. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 8
    - LCS 2005 SP1 Server-Side Contact Lists Not Populating within Communicator 2005 I am having trouble creating server-side contact lists for my employees using Communicator 2005. We have an LCS 2005 SP1 SE virtual test environment. All my users are LCS enabled (right-clicked on OU within AD to enable). Background knowledge... Contacts.txt looks like... msRTCSIP-PrimaryUserAddress sip:u ser1 @domain.local sip:u ser2 @domain.local sip:u ser3 @domain.local ACESFile looks like... all allow allow General I then run the following commands at the command line... CScript LCSAddContacts.wsf /userfile:contacts.txt / contactsfile:contacts.txt Then I run... CScript LCSAddaces.wsf /userfile:contacts.txt / contactsfile:acesfile.txt The command line displays the examples within the script, pauses for about 2 seconds, and then completes. However, when I have user1, user2, etc log into Communicator 2005 nothing changes... Please help, Keenan Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 9
    - run an exe file in excel using VB Hi, I want to run an exe file in excel using VB, any simple coding for that Thanks. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 10
    - Some questions about WER Hi! I'm integrated WER support in my Delphi 7 Application and now get some results 1) I'm register winqual account 2) Integrate WERAPI support in my application (Delphi program has own exception processing) 3) Map my .exe and upload xml file procedure TForm1.ApplicationEvents1Exception(Sender: TObject;E: Exception); var eventType : PWideChar; begin if E is EAccessViolation then begin ... eventType := 'Crash32'; WerReportCreate(eventType, WerReportCritical, @werReportInfo, @hReportHandle); ... end; end; I'm read this forum and Claus Brod's blog ( http://www.clausbrod.de/cgi-bin/view.pl/Blog/DefinePrivatePublic20070625 ) and get value of eventType - "Crash32" for common exception types. Is this right After that, I'm raise sample Access Violation (24.08.2007) delphi code submitOptions := WER_SUBMIT_OUTOFPROCESS or WER_SUBMIT_NO_CLOSE_UI; Res := WerReportSubmit(hReportHandle, WerConsentNotAsked, submitOptions, @submitResult); Res = S_OK, submitResult = WerReportUploaded and get reports with Buckets ID = 74072866, 72917002! Are these Buckets ID valid But today, 27.08.2007 I'm don't see any reports in winqual software homepage! How long time wait for get report in winqual I want to check my delphi code for valid WERApi translate. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 11
    - public variable between sheet code and userform Hey, I created a public variable in sheet code and assigned a value to it. Now I want to be able to call a userform from the sheet code and use the value I established in the Userform code. Is there a way to do this Tag: Help Deploying VBA changes across hundreds of Word Temlates
- Next
  - 1
    - A question from a beginner... Hi all: I am new to the programming world, and I am interested in learning VBA. So, please provide some tips where should I start Thanks in advance. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 2
    - About iexplorer 7 error. "The instruction at 0x66869bc7 referenced memory at 0x00000050 . The memory could not be read." I keep getting this message whenever I close the internet explorer 7. Can anyone help me out on this matter Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 3
    - Excel Pivot Table Hi, I am populating the Excel Pivot table from the sql OLAP cubes . I am having 2 items in the Data Field list and I dragged and droped those two in the pivot table and every thing is working fine .But the problem is when i am selecting two rows and asking to group by right clicking the Data field , It is grouping the rows but it is showing only one column insted of two and the 2nd column is disappering and it is also disappering from the Data Field List and coming back when i say unGroup. I could able to figure out if it is a front end (Excel ) issue or it is a backend (OLAP) issue. Can any one help me on this and also let me know if I am missing any thing in setting the properties. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 4
    - VBA Word Can't find project or library I have wrote a VBA macro and in the macro I use visual basic functions such as "RTRIM$( )" and "STR$( )". When running the macro on a client's computer the error "Compile error Can't find project or library" comes up and it stops at the "Rtrim$( )" function. What is missing on the client's computer. Please help Thanks Paul Seo Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 5
    - Excel: Public variable doesn't work I have a routine that needs to sometimes display a form to get user input, then hand the result back to the caller. I use a Global variable to do this but the value is not returned. When I use Option Explicit, I get a run-time error, "Variable Not Defined" in the form. The code is like this: In the calling module... Code Snippet Public PsResult as String private sub ModuleA() PsResult = "" formB.show msgbox PsResult end sub Then, in the form's code Code Snippet PsResult = "derived value" Note that the form doesn't specify "Public PsResult as String". It shouldn't need to as it should already exist. What is important to note is that my calling code is within ThisWorkbook (because it's replacing the standard Excel Workbook_BeforeSave function). If I copy a bit of it to a new Module then it works perfectly. So why can't my form see a Public variable defined in ThisWorkbook Jeff. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 6
    - Inserting Comments in Excel...from a Beginner Hello everyone. I'm new to VBA programming, but I have done a little. I have a new project at work that I'm trying to accomplish via VBA, but I'm not sure how to get it done. Thanks in advance for your feedback. My goal is to take data from a cell on Sheet2 of a workbook, and place that data in a comment field in the exact cell on Sheet1 in the same workbook (data in A10 on sheet 2 will be a comment in cell A10 on sheet 1). I will have to create a loop to do this in several columns and rows, but I'm pretty sure I know how to do that. But how do I insert that type of comment, if it's possible at all Thanks! Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 7
    - Time Addition Hi all, I am facing such a horrible problem, i have tried everything and cannot find a way to solve it and it is now driving me insane. What i am trying to do is to calculate the amount of hours a member of staff has worked for the whole week, this is done through VBA on a userform and i just cannot do it. the main problem i am facing is how to add hour and minutes together to give me a value in format HH:MM see code below Code Snippet Dim BreakLength As Date Dim TotalHours As Date 'Dim for Sunday Dim TimeAstart As Date Dim TimeAend As Date Dim TimeAelapsed As Date Dim Day1 As Date 'Dim for Monday Dim TimeBstart As Date Dim TimeBend As Date Dim TimeBelapsed As Date Dim Day2 As Date 'Dim for Tuesday Dim TimeCstart As Date Dim TimeCend As Date Dim TimeCelapsed As Date Dim Day3 As Date 'Dim for Wednesday Dim TimeDstart As Date Dim TimeDend As Date Dim TimeDelapsed As Date Dim Day4 As Date 'Dim for Thursday Dim TimeEstart As Date Dim TimeEend As Date Dim TimeEelapsed As Date Dim Day5 As Date Dim TimeFstart As Date Dim TimeFend As Date Dim TimeFelapsed As Date Dim Day6 As Date 'Dim for Saturday Dim TimeGstart As Date Dim TimeGend As Date Dim TimeGelapsed As Date Dim Day7 As Date BreakLength = "00:30:00" '------------------------------------------------------ 'this section is repeated seven time all together 'once for each day of the week 'label8 for sunday, label9 for monday and so on 'TimeAxxxxx for sunday, TimeBxxxxx for monday and so on If Label8.Caption = "--:--" Or Label8.Caption = "" Or Label8.Caption = "00:00" Then Day1 = 0 Else TimeAstart = ComboBox1.Text TimeAend = ComboBox2.Text TimeAelapsed = TimeAend - TimeAstart + IIf(TimeAend > TimeAstart, 0, 10) If TimeAelapsed >= "07:00" Then Day1 = TimeAelapsed - BreakLength Else Day1 = TimeAelapsed End If End If '----------------------------------------------------- 'Here is where all the problems start, to TotalTime = Day1 + Day2 + Day3 + Day4 + Day5 + Day6 + Day7 Label15.Caption = Int(CDbl(TotalTime * 24)) & Format(TotalTime, ":nn") if my shift goes over midnight then the number of hours is in 3 digits e.g if my shift is 21:45 to 06:00 then label15 shows 223:45 instead of 7:45 (note that is should be 8:15 but the break time is deducted) but label8 will show me 7:45 So the main solution i am looking for is how to add time together. I am now desperate for a solution to this, so if anyone does have a solution please help me Thanks in advance Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 8
    - Excel/Word VBA IDE - Additional Controls Dialog Box refuses to display I have a problem trying to add additional controls to the Controls toolbox The two methods I've tried 1. Right click on the controls toolbox , click Additional controls 2. Menu, Tools, Additional controls Neither of the above produce a dialog box containing the list of additional controls to choose from. Things I have tried so far.... Office Update, Reinstalling Office 2000, uninstalling Office 2000 and installing Office XP (2002) Please help any ideas Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 9
    - Excel Time Help Hi all, This is my first time posting here , hope i will be able to find the answer i am looking for. I'm very new to vba so please bear with me. Here goes, this is the problem i'm having at the moment with my formulas and vba. I am currently desinging a roster for my workplace and the problem i am having is to work out the night shifts. these night shift start at 23:00:00 and end at 06:00:00, now because there is a change in date between 23:00:00 and 00:00:00 im finding it very difficult to find the appropriate formula. Let say the shift worked is starting at: 22:00:00 and ending at 07:00:00, what i would like excel to do is to calculate how many hours are worked from 22:00:00 to 23:00:00 and from 06:00:00 until 07:00:00 in one cell and in a different cell the time worked between 23:00:00 until 06:00:00, bearing in mind that the finish time can be before 06:00:00 and the start time could be after 23:00:00. Any one can help me please any help would greatly be appreciated. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 10
    - Excel import I have a working Excel vba that imports about 50,000 cell values from a variety of different text files into numerous worksheets. The problem is it is painfully slow, taking over an hour to run. The main time killer is populating each individual cell using the format: Worksheet.Cells(row,col).Value = strValue Commenting out these lines of code the program runs in under a minute. I've been searching for other alternatives but nothing I've found executes any faster. Ideally I would like to populate one row at a time using a delimited string, or string array. Other options I've considered but haven't done yet are creating a .csv file for each sheet then importing that, or using ADO from an external program. I'm looking for any suggestions on how to speed up this application. Thanks. Rod Wing Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 11
    - Use of InStr() function Can you use InStr() to determine the position of the next character in a string that is not equal to one of a number of options. If so, how For example, what would be the syntax of an expression that returned the position of the next character that was not either a "t", space, tab or return character Would you have to use ascii representations of some or all of these characters in the exepression Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 12
    - iexplore.exe - Application Error (EVERY TIME I CLOSE IE7) :( Hi, I am absolutely clueless as to why this keeps happening, but it is driving me insane!! The exact error message reads: 'The exception unknown software exception (0x0eedfade) occurred in the application at location 0x7c81eb33.' Could anyone shed some light on maybe what the cause is and/or how I can fix it Thank you in advance to anyone who reads and/or offers any information. Laura x Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 13
    - Importing excel spreadsheets over time into Access using a module in Access Hi, I am trying to create an access database that will allow to import excel spreadsheets at the click of the button on a switchboard. I have been trying for hours to create a module in access that will import excel spreadsheets that contains a table with 12 columns and a different amount of rows for each spreadsheet. I was able to create an almost complete macro in excel that I copied over to an access module (i was unable to create a Do while that will format the excel file by cutting out the first 8 rows and take out all columns except for 6 of them that i need for importing. It should delete rows below once it finds a row with "Elimination Totals" (Column C) or delete rows belowonce it reaches a blank cell in Column A. The Functionality of Database that I am trying to create can be described as: Functionality: Import Spreadsheet (6 fields/columns) into Access Table with 8 fields(Year & Quarter fields will be entered in by user) Use macro or module to format the imported file a) auto expand all columns and rows b) Delete first 8 rows of spreadsheet c) (only import columns A, B, C, D, I, & L) delete all of the rest of the columns d) Read Column B data (truncate or delete the first character in the cell) ex 6050 should be 050; G123 should be 123: keep the remaining three characters in the table. e) Import the table until Column A reaches a blank value �� (maybe a do while function��.not sure how these work ) or until Column C = ��Elimination Totals�� Everything on the Elimination Totals line and below should be deleted and not brought into the table in Access. f) Row 1 & 2 values should be combined to make one row. Example: Row 1 Column A = Cost; Row 2 Column A = Center; Combine the two and you get one Row = Cost Center. Delete the extra row The import should be initiated by clicking ��Upload Spreadsheet�� on the Switchboard (I believe I just need to refer to the function name of the import module to accomplish this) a) When the ��Upload Spreadsheet�� button is clicked on the Switchboard, a query should be initiated that requires the user to input the Year value ex: ��2007�� and Quarter value ex: ��1��. These fields as well as the imported fields should be linked to a table. The table should be pulled by a form preferably a Pivottable sorted by Fiscal Year and Quarter that allows user to change the Agreement Description field(Column C from above) and RA Number (Column L from spreadsheet before formatting) but the other 4 fields should show all and not be able to get modified at all. Data in the Pivottable will be the Agreement Amount value (Column D from above) b) Another Pivottable will be needed to be sorted by Component (Column I from spreadsheet before formatting). Only the Components should be accessible to change for sorting options here; the rest of the fields should display all and be locked from editing. Data in this Pivottable should also be from the Agreement Amount field/column(Column D from above). The macro that i used is as follows: Sub ProviderListMacro() ' ProviderListMacro Macro - Formatting Range("E9 20").Select Selection.EntireColumn.Delete Range("I8 42").Select Selection.EntireColumn.Delete ActiveWindow.SmallScroll Down:=21 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 ActiveWindow.SmallScroll Down:=-45 Rows("1:8").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "Cost Center" Range("B1").Select ActiveCell.FormulaR1C1 = "Agreement Number" Range("C1").Select ActiveCell.FormulaR1C1 = "Agreement Description" With ActiveCell.Characters(Start:=1, Length:=21).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("D1").Select ActiveCell.FormulaR1C1 = "Agreement Amount" Range("E1").Select ActiveCell.FormulaR1C1 = "Organization" Range("F6").Select Selection.EntireColumn.Delete Range("F5").Select Selection.EntireColumn.Delete Range("F1").Select ActiveCell.FormulaR1C1 = "RA Number" Rows("2:2").Select Selection.Delete Shift:=xlUp Cells.Select Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("B10").Select ActiveWindow.SmallScroll Down:=-9 End Sub Can someone please assist me on successfully giving the user the ability to import an excel spreadsheet (has the same required information) by creating a module that will import the file and fix the formatting, then save the information to a table and display it on a Form The form should have two extra fields - Year(2007) and Quarter (ex: 1,2,3,4), which i can/will add to the table. Any input will be helpful thanks, ultsun1 Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 14
    - Dynamically add controls with events at runtime Hello, I want to add new buttons to an existing userform at runtime and link event handler to them so that the buttons do something. I've tried several ways that I've found online and managed to create the button and to programmatically add the code for the click event but when i click the button nothing happens. The button and click event sub code is created fine. I have added the reference to Microsoft VBA Extensibility but something is missing. Please, I am completely stuck and would appreciate any help! Thank you. Here's the code I have so far: Dim myCmdObj As CommandButton Dim i As Integer Private Sub CommandButton1_Click() Dim NName As String i = i + 1 ' Set the name for the button NName = "cmdAction" & i ' Add button Dim N As Long Set myCmdObj = frmMain.Controls.Add("Forms.CommandButton.1", NName, Visible) With myCmdObj .Caption = NName .Left = 20 * i .Width = 20 .Top = 20 End With ' Inserts code for the button With ThisWorkbook.VBProject.VBComponents(5).CodeModule N = .CountOfLines .InsertLines N + 1, "Private Sub " & NName & "_Click()" .InsertLines N + 2, vbNewLine .InsertLines N + 3, vbTab & "lblTest.Caption = ""BUTTON CLICKED""" .InsertLines N + 5, "End Sub" End With End Sub Please help me!! Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 15
    - run an exe file in excel using VB Hi, I want to run an exe file in excel using VB, any simple coding for that Thanks. Tag: Help Deploying VBA changes across hundreds of Word Temlates
  - 16
    - autofill vba code I need to copy a function down a column to length of the adjoining column to the left. I recorded a macro which did what I wanted. Unfortunately, the column changes sizes depending on my data. Unfortunately, every time I run the macro it uses the length that was recorded the first time. How can I set up a dim or something to have the autofill work with the variable length lists Thank you very much for any help nsheff1@hotmail.com Tag: Help Deploying VBA changes across hundreds of Word Temlates

SiobhanO

I have been asked to "clean up" existing Word templates that have VBA code in them. Basically what I need to do is to remove the old code that is currently in the ThisDocument class module of the template and replace it with the new code.

I've exported the "good" code to a .bas file and I have worked out how to import it back into the Word Template but the only problem I have is that I am importing it as a new module and the original code for the template is stored in the ThisDocument module. I don't know how to either clear the ThisDocument module programmatically (using VBA) or how to replace the contents of it with the "good" code.

There are hundreds and hundreds of these templates and I am looking for the quickest and easiest solution as I am only new to the whole VBA world.

Any help greatly appreciated.

Thanks in advance.

SIobhan

Top

Re: Help Deploying VBA changes across hundreds of Word Temlates

Derek Smyth

Hi,

Thats a nasty one mate, but it could be possible. Your going to need to write something that selects the word template, or ideally a group of templates, and then opens each one and modifies the VBAProject.

Have a look at this excellent article

http://www.cpearson.com/excel/vbe.htm

It gives examples of how you can write VBA code that modifies VBA code. Select a group of templates that you want to modify the same way, open each template, modify it's VBA to whatever you need, and then save it. Hopefully that should do the trick.

Top