Set old variable from a derived variable in VBA
Would it be possible to set the find and replace text to whatever the new variable "path" is so that next time the macro runs it now looks for the new path instead of the original path from the first find and replace?
Sub Code() ActiveDocument.ActiveWindow.View.ShowFieldCodes = True Dim oldpath As String Dim path As String oldpath = ActiveDocument.path path = Replace(orgpath, "\", "\\") Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "C:\\Users\\Gianni\\Desktop" .Replacement.Text = path .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll ActiveDocument.ActiveWindow.View.ShowFieldCodes = False End Sub
I update a specific path of LINK codes in order to keep the objects active when the folder gets moved around. However, if I move the folder more than once, the macro above would break because it is searching for path A, the first. It replaces it with path B. But, if I want to move it again, searching for path B to get path C, it is still searching for path A.
Set a Custom Property in the document to store the path. From memory:
Document.CustomPropertiesis the collection you want to check and manipulate.
See also questions close to this topic
Access VBA to copy partial text from text box, similar to MID from excel
I have a text box that populates when searching for a members information. The text box populates with a claim # in the format of: A999999999999001 where the 9's could be any # but the letter A and the 001 will always be consistant.
In excel you can use the formula =mid(cell reference, starting character, # of characters) to extract the middle portion of the text..
is there a way to do this in Access? I want to create a "COPY" button that will allow users to copy just the 9's from the claim # to the clipboard and drop the A and 001's.
The claim # must maintain its full format in the tables, however the reduced portion is used in another program to pull up specific information about a transaction.
Any help would be greatly appreciated.
Disable all paste functions for the following vba code
I'm running a target intersect method in excel to prevent a user from pasting over validation cells, problem is, I didn't account for all the paste methods, and pastespecial overrides the cell validations.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("I14:J1000")) Is Nothing Then On Error Resume Next ' In case there's been no previous action ' Check if the last action was a paste If Left(Application.CommandBars("Standard").Controls("&Undo").List(1), 5) = "Paste" Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End If End Sub
I used the code below and binded it to Ctrl V, so that it matches the destination formatting, but it overrides the cell validation from the previous code:
Sub PasteWithDestinationFormatting() ActiveCell.PasteSpecial (xlPasteValues) End Sub
I'm assuming I need to change ="Paste" to a "PasteSpecial" but it didn't accomplish what I wanted it to do, most likely cause I didn't have the correct location of the PasteSpecial object.
Thanks for the help!
Clicking dropdown arrow of Excel Data Validation entry no longer enters data into cell
So I have been following this video to prepare a data validation list that is able to auto-complete user entries: https://www.youtube.com/watch?v=vkPoViUhkxU
Essentially at about 10 mins, the instructor demonstrates that when the user types in a letter or part of a word into a data validation cell, when the drop-down of the data validation is clicked this produces a dynamic list of all possible entries.
I am able to produce a dynamic list, but the list is only created when I click off the cell not when the dropdown arrow is depressed as in the video. I was wondering if I am missing a setting here to allow data to be entered into a cell with the dropdown, not just on pressing the enter key?
Any thoughts or guidance would be greatly appreciated,
how to create Word Reports in razor
I need to create Word (or Excel) reports in Razor Pages. What would 3rd party software you recommend?I need robust functionality but restricted with $. For now, it is 1 website.Thanks!
Populate a UserForm Label Caption with text from an Enclosed Bookmark using VBA
I'm creating an MS Word document with a table at the top with cells for 'Name', 'Date', 'Skills Verified' etc. and 10 questions in separate sections below this.
I'm creating a UserForm to make it simpler for all participants to complete the assessment on their PC's.
I'm using a MultiPage to show each question on a separate page of the UserForm and to display the question I'm attempting to populate the Caption of a Label by referencing the question on the document using an enclosed bookmark.
Please note: Other staff may edit or manage this document in future and most of them are not experienced at all with VBA or UserForms which is the main focus for what I'm attempting (plus it's efficient right?).
I've read up on bookmarks (wordmvp) and I understand how to set the bookmarks fine.
I can't work out how to get the contents of an enclosed bookmark to populate the Label Caption on UserForm initialization.
I've looked at the following info but haven't been able to make any sense of it. https://msdn.microsoft.com/en-us/vba/word-vba/articles/bookmarks-object-word https://msdn.microsoft.com/en-us/vba/word-vba/articles/captionlabels-object-word https://forum.solidworks.com/thread/209704
Assuming default names are used in the UserForm and the question "Is this a test question?" is in an enclosed bookmark named "bookmark1" in my document; below is the closest working code I could come up with.
Private Sub UserForm_Initialize() Dim question1 As String question1 = ActiveDocument.Bookmarks("bookmark1") With Selection Label1.Caption = question1 End With End Sub
However this displays the string "bookmark1" in the label1 caption, not "Is this a test question?".
I can't work out how to edit the code to make it show the text from the bookmark rather than the string "bookmark1" in the label1 caption (I'm not even sure if it's possible?).
Can anyone share where I've gone wrong and explain how to amend this?
Powershell: The right commandline to set a ListTemplate via powershell
Ladies and gents,
I have an issue with a bit of code to create a word document, fill this with some lines of text, creating a list (numbering, 1., 1.1, 1.1.1, etc) and then creating an index. (
$iis part of a for loop)
This works amazingly well when I just use the following line of code:
The output is then:
1., a., i.
For some reason it defaults to 'single level' lists if I put down:
Resulting in the output:
1., 2., 3.
However, using the below code obviously doesn't work, because I need a
ListTemplateobject to apply to the format, but I can't find any specific way to create that object in Powershell. There's some VBA examples, but I seem incapable of translating this to Powershell.
The intended end-result has to be 1., 1.1., 1.1.1. ... (Obviously the bullet2 style is just an example, the question is how do I create the ListTemplate object in Powershell).
Using Excel Match function in Word VBA
I had a Word VBA macro that worked perfectly to populate data from an Excel workbook (which is acting as the 'database'). It called the excel Index and Match functions. However, as of today, I repeatedly get an error telling me that the Match and Index functions could not be found. What happened? Code is below.
On Error Resume Next Set xlapp = GetObject(, "Excel.Application") If Err Then bStart = True Set xlapp = CreateObject("Excel.Application") End If On Error GoTo 0 Set xldocbook = xlapp.Workbooks.Open("D:/Dr list.xlsx") Set xldocsheet = xldocbook.Worksheets(1) 'Set the current selection With xlapp.WorksheetFunction IUH = .Index(xldocsheet.Range("I:I"), .Match(DrName, xldocsheet.Range("E:E"), 0)) If IUH = "N" Then Address = .Index(xldocsheet.Range("K:K"), .Match(DrName, xldocsheet.Range("E:E"), 0)) CityStateZip = .Index(xldocsheet.Range("L:L"), .Match(DrName, xldocsheet.Range("E:E"), 0)) Else Address = "" CityStateZip = "" End If Phone = .Index(xldocsheet.Range("F:F"), .Match(DrName, xldocsheet.Range("E:E"), 0)) Fax = .Index(xldocsheet.Range("G:G"), .Match(DrName, xldocsheet.Range("E:E"), 0)) FullAddress = Address + " " + CityStateZip Debug.Print "Full Address is" + FullAddress End With xldocbook.Close If bStart = True Then xlapp.Quit End If
VBA to add a row to the bottom of the table and insert Rich Text Content Control
I have a form in Word 2013. In it are various tables and I want users to be able to add a row to the bottom of the table. I have done this with the following code:
`Dim oTable As table Dim oCell As Cell Dim oPrevRow as Row, oNewRow As Row Dim iColumn As Long Set oTable = ActiveDocument.tables (1) Set oPrevRow = oTable.Rows(oTable.Rpws.Count) oTable.Rows.Add Set oNewRow = oTable.Rows(oTable.rows.Count)`
What I want is for all 7 cells in that new row to have Rich Text Content Control inserted into them. How do I do this?
Runtime error '70': Permission Denied VBA word
I am trying to create a countdown timer in word.
While running the code i am getting "Runtime error '70': permission denied" for a line in the code:
time = time + TimeValue("00:00:01")in modtimer module.
I have created a userform and a separate module named modtimer, the code is given below:
The word document code: (ThisDocument) to show timer when word document opens
Sub Document_Open() UserForm1.time_left.Visible = False UserForm1.Label1.Visible = False UserForm1.Show End Sub
The code given in userform is as follows:
Dim g_start As Variant Dim start As Variant Dim timeEnd As Variant Dim g_time As Variant Dim time As Variant Dim g_timeEnd As Variant Dim time_duration As Variant Dim g_time_duration As Variant Dim g_End As Boolean Private Sub btnStart_Click() start = Now g_start = Format(start, "hh:mm:ss") timeEnd = Now + TimeValue("00:30:00") g_timeEnd = Format(timeEnd, "hh:mm:ss") time_duration = timeEnd - start g_time_duration = Format(time_duration, "hh:mm:ss") Label1.Visible = True time_left.Caption = g_time_duration time_left.Visible = True btnStart.Visible = False time = start + TimeValue("00:00:01") time_duration = timeEnd - time g_time_duration = Format(time_duration, "hh:mm:ss") time_left.Caption = g_time_duration modtimer.time_count End Sub
The module code is as follows:
Sub time_count() If time_duration = TimeValue("00:05:00") Then MsgBox "Only 5 minutes remaining", vbInformation End If time = time + TimeValue("00:00:01") time_duration = timeEnd - time If time_duration = TimeValue("00:00:00") Then g_End = True End_Exam End If 'If g_End = True Then ' End_Exam 'End If g_time_duration = Format(time_duration, "hh:mm:ss") time_left.Caption = g_time_duration Call time_count End Sub Sub End_Exam() MsgBox "Examination Time has Expired, Click Ok to Submit", vbCritical 'Documents.Save False, wdOriginalDocumentFormat Documents.Close wdPromptToSaveChanges, wdPromptUser End Sub
What is the reason behind it? Can anybody help?