thank you for watching my video. Because I am not able to put square brackets in the description of video, I have replaced the with ||field_name|| in the code in the description.
Good Example. I noticed where you had a second instance to replace the same text that you put in a second set of code for the next instance. Could you not use a replace all to replace all instances of the same text?
could I instead name each cell value I need and call on those instead? for instance in excel I change the name of cell r-1 to "Name". Then in the code could I instead replace r-1 with "Name"@@BarbHendersonconsulting
Great video barb, very helpful as a beginner. I do have a question though, how do i go about saving the file without keeping the file open? I just want to have the script do all the text replacement without needing to open them all, because i have like 40 rows, so that is 40 files remained open on my computer that i have to close manually. Or if it does "need" to open them all, at least close them once done, any idea?
Hi, I run the code as exact as you have it but get an error message from the off even trying to open the word document from vba code. Do you have any ideas what code be causing the issue?
The only thing I can think of is your references. On the vba window if you go to tools - references. These are the references that I have checked. *Visual Basic for Applications *Microsoft Excel 16.0 Object Library *OLE Automation *Microsoft Office 16.0 Object Library
I loved this tutorial! Question - if I wanted to create something that many people could use. Is there a way to get around them having the same the word template on their computers? Or having to adjust the macro to match the word template location?
Wouldn't it be easier to just use the Mail Merge functionality to link particular cells in the Excel sheet to specified fields in the Word document? No need for any VBA code or macros. You can then print out all or specific records either to new individual DOC files or directly to a printer.
I've tried mail merge method. It's good for something you want to print immediately, as is. But if you want to create a document with some additional pages, or if you need to set some conditions, this method is much better and more flexible.
@@finhas8865You can make the mail merge as separate documents - one doesn't necessarily need to print them. The output can be sent to a multi page Word Doc. However, every person has their own scenarios and needs, so absolutely do whatever helps with your own needs and workflow. Cheers.
This is a great breakdown and really helpful, can you help me understand how can I do this using google sheets excel? Can I use the same script for Google Excel?
Thanks Barb. Your solution is working just fine! Do you know how to populate fields on headers? It seems to work only if the fields are included in the main body of the Word Template.
With a few small edits to Barb's code (thanks to ChatGPT) I got it working after copying and pasting my template onto as many pages as I needed. You could probably automate the copying of the template into as many pages as you need, but I didn't bother for my short dataset. Sub ReplaceText() Dim wApp As Word.Application Dim wdoc As Word.Document Dim custN, path As String Dim r As Long Dim pageNum As Integer r = 2 pageNum = 1 Set wApp = CreateObject("Word.Application") wApp.Visible = True Set wdoc = wApp.Documents.Open(Filename:="file path of word template", ReadOnly:=True) Do While Sheet1.Cells(r, 1) "" With wdoc .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 1).Value .Application.Selection.EndOf .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 2).Value .Application.Selection.EndOf .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 3).Value .Application.Selection.EndOf .Application.Selection.Find.Text = "" .Application.Selection.Find.Execute .Application.Selection = Sheet1.Cells(r, 4).Value .Application.Selection.EndOf ' Insert a new page .Application.Selection.InsertNewPage ' Increment the page number pageNum = pageNum + 1 End With r = r + 1 Loop ' Save the final modified document custN = "Progress_Report" path = "file path to save document" wdoc.SaveAs2 Filename:=path & custN & ".docx", _ FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False ' Close the Word document and quit the application wdoc.Close wApp.Quit ' Release the object references Set wdoc = Nothing Set wApp = Nothing End Sub
Dear Barb, Thank you very much for your tutorial! It is exactly what I was searching for. But If I have 500 rows in my excel file and today I need to populate templates, for example, from 23rd to 54th row, tomorrow I will have to populate another row range. My question is - what should be inserted into the code to proceed only part of the rows?
Hey Barb! Excellent video. Really helpful. But I have a single field repeating atleast 10 times in the template. How to replace all of them in one shot?
you can install this code ActiveDocument.Bookmarks("\StartOfDoc").Select to move to the top of the document and then loop until all the fields are filled.
Hi Barb! When i click run, there does not appear to be any files created. I checked the file path and it's correct. I cannot figure out why it's not generating an error or docs
@BarbHendersonconsulting thanks Barb! I adjusted the code so it reads (r, "A") and is now pulling all the correct data and generating the docs, however now I can't figure out why it is populating the top of my doc with the info instead of the square bracketed text as indicated in the code... 🤦♂️
@@BarbHendersonconsulting thank you but this is not what I want to do. I have a word template with one page and data in Excel. I want to do something like a mail merge and extract all the data to that word template
Hi Barb - great tutorial. My Q is I'd like to use the data input on excel in vertical format so headers would be in column A and the input would be in Column B - how would I adjust the code?
Hi there! For some reason, the Word documents the macro creates will only save to a mysterious 'template' folder I can't seem to find on my computer, even though I put in the path to save to a different folder. Any suggestions?
Thanks for this it was really useful. I used it to auto generate reports but as each line feeds down looking for the next field name how would you get it to check the header for one?
@@BarbHendersonconsulting Hi, don’t think I was very clear. In the word template I have a for document number but it’s in the header and appears to be missed. I have read a few threads and they appear to use headerfooterindex to search and add things to the headers?
This has been EXTREMELY helpful! Thank you. I am looking to create if logic based on information within the cell - do you have any examples? For example: If the cell has "blocking issues: 1" write BLOCKED on the word template.
Hey Barb! Great Video and very educational. I am having a 5152 Error popping and when I debug it - it points out .SaveAs2 Filename:=path & custN, _ FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False The bug says that it's an invalid location but not sure what's wrong with the code as it's the same as yours. Please help and thanks!
The code would work if you would like to save it to my computer! To determine the path go to the file folder where the file is and go to the menu bar and select copy path. Then you can replace my path with your path.
@@BarbHendersonconsulting I did put in my own pathway - This is what I have: custN = Sheet1.Cells(r, 1).Value path = "C:\Users\jared.bagnall\Desktop\ALPHA\" .SaveAs2 Filename:=path & custN, _ FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
What options are available, due to data security issues, to not use One Drive? Can I use just a local or private server file location for my Word macro/template?
Hi Barb, it is extremely helpful. Thank you so much for this. If Word document needs to be named "Mickey Mouse some place" (name+address), how should this be changed correctly? custN = Sheet1.Cells(r, 1).Value Appreciate your help.
@BarbHendersonconsulting "custN = Sheet1.Cells(r, 1).Value" creates document name of only one value (column 1). If document name must consist of column 1 and column 2 of each row, how this should be done?
I got this to work, then adjusted it to do a different sheet. On that sheet, it will only do a few rows (it changes how many each time) then it fails. It says the issue is on .Application.Selection.Find.Text = "" and gives me "Run-time error '462': The remote server machine does not exist or is unavailable." What would you suggest I try?
Is there a way to have a "Save As" window pop up when it is run, so the user can designate where the word doc is saved? I plan to use this macro for multiple people to use across multiple projects, so saving the document to the same file location would not make sense in my case.
Hey Barb excellent tutorial I got everything running great based off this and added a few line to add more data point! The problem I can't seem to solve is with taking the number formatted as accounting or currency in Excel but it displays as a generic number in Word. I've played (trial and error) with NumberFormat = but I can't seem to tackle this. Any suggestions on what I should research to resolve?
Hey great video. I used your code and it works great but now I want to modify it so that it only applies to visible rows when I have a filter applied. Any suggestions?
Hi Barb, I am still getting "run-time error:'424': Object required" when i add the command do while sheet1.cells(r,1) "". Any help/Tips/clearification?
you might not have all the references that you need. On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked
Thank you for the video! I have an issue where when I used the code to create the saved documents with the merge fields, the merged information is not in the correct location and the merge field names are still present (the info i wanted shows at top of page and merge field names are still in table). The preview in the template works fine, but the saved documents are incorrect. Any suggestions?
Ensure that you have theses lines at the bottom r = r + 1 Loop if you have those f8 through your code and make sure that the r is increasing in the code.
@@BarbHendersonconsulting Thank you for the reply! I do have these lines at the bottom and my r is increasing in the code. It properly pulls the data from the first row and properly transfers it to the Word template, but then stops and throws the following error, Run-time error '9105': Application-defined or object-defined error.
What you could do is name your fields in Excel. For example Name=sheet1.cells(r,3).value and then just before the r=r+1 you could refresh the values Name="".
My expertise is Excel not Word, this is my best guess ActiveDocument.ExportAsFixedFormat OutputFileName:= _ path & custn.pdf ExportFormat:=wdExportFormatPDF
Hi Barb, your video is a lifesaver, it is brilliant! However, i am having an error with this part ".SaveAs2 Filename:=path & custN, _ FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False", i am using Microsoft Office Professional LTC 2021...I cant seem to save, name the file due to fileformat...
On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked
I was getting the "5152" error because I was trying to create filenames from the value of a cell that contained characters that aren't allowed. Like colons. I replaced these with hyphens and it seemed to work.
Hi Barb! Great video! Very instructive and broken down and explained very carefully. When trying to run the macro, I seem to be getting a "Compile error: user-defined type not defined" for the the line of code reading "Do while Sheet1.Cells(r, 1) (not equl to)""....What could I be doing wrong. Need your guidance. Thanks.
@@BarbHendersonconsulting today I finish the macro, its easy i use a word document with some especific words and the macro look for them and paste the text or charts from excel
two reasons might be: 1.I am not able to place symbols in the description of the video. the command should read do while sheet1.cells(r,1) "" 2.On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked
Thank you Barb, great video! 1) I am experiencing an issue that, it write the data from sheet directly in one line like "Donald Duck11112Vancuver..." Do you have idea why? 2) would it be possible to first find every occurrence of in dotx, replace, and just after that move to another cell (f.e. )? How would you write it? Thank you
1) I think that it has to do with the formatting on your word document. it replaces the field exactly were it is found. 2)The way I do it does not allow the software to overwite by accident.
even i am facing the same issue. Instead of finding the and in the word document, it just displays continuously in the beginning "Donald Duck11112Vancuver" in one line. Let me know if you have any solution.
the line Set wdoc = wApp.Documents.Open(Filename:="C:\Users\screa\OneDrive econstructionBH\Documents\Excel\welcome.dotx", ReadOnly:=True) The read only prevents the word template from being changed
![Gucci Mane & Sexyy Red - You Don't Love Me [Official Music Video]](http://i.ytimg.com/vi/j7IBqyFKT4c/mqdefault.jpg)
thank you for watching my video. Because I am not able to put square brackets in the description of video, I have replaced the with ||field_name|| in the code in the description.
FYI, these are technically "angle brackets". Square brackets are on the same keys as the curly braces { }.
Good Example. I noticed where you had a second instance to replace the same text that you put in a second set of code for the next instance. Could you not use a replace all to replace all instances of the same text?
This is extremely confusing you keep saying square brackets [ [ ] ] but then display > and || || - neither of those are square brackets
thanks Barb!! saved me from writing 27 letters!
Happy to help!
very useful tutorial.thank you!
Glad it was helpful!
This is cool Barb! Can't wait to try this out!
thank you
Great video! I have been looking for a video like this for days. Does this work with multiple worksheets within a single workbook?
you would have to include the worksheet name as well as the cell reference
could I instead name each cell value I need and call on those instead? for instance in excel I change the name of cell r-1 to "Name". Then in the code could I instead replace r-1 with "Name"@@BarbHendersonconsulting
Nice video 👍 thanks for sharing
Thanks for visiting
Hi Barb! This is great thanks. Would it also work to take data from Excel and put it into a formatted table in word?
Yes, absolutely, Check out this video ruclips.net/video/OziXNs038xc/видео.html
Great video barb, very helpful as a beginner. I do have a question though, how do i go about saving the file without keeping the file open? I just want to have the script do all the text replacement without needing to open them all, because i have like 40 rows, so that is 40 files remained open on my computer that i have to close manually. Or if it does "need" to open them all, at least close them once done, any idea?
I think it would be best to keep it open
Hi, I run the code as exact as you have it but get an error message from the off even trying to open the word document from vba code. Do you have any ideas what code be causing the issue?
The only thing I can think of is your references. On the vba window if you go to tools - references. These are the references that I have checked.
*Visual Basic for Applications
*Microsoft Excel 16.0 Object Library
*OLE Automation
*Microsoft Office 16.0 Object Library
I loved this tutorial! Question - if I wanted to create something that many people could use. Is there a way to get around them having the same the word template on their computers? Or having to adjust the macro to match the word template location?
it probably would be best if you had a copy in numerous locations.
Wouldn't it be easier to just use the Mail Merge functionality to link particular cells in the Excel sheet to specified fields in the Word document? No need for any VBA code or macros. You can then print out all or specific records either to new individual DOC files or directly to a printer.
another option
I've tried mail merge method. It's good for something you want to print immediately, as is.
But if you want to create a document with some additional pages, or if you need to set some conditions, this method is much better and more flexible.
Everyones a critic.
@@finhas8865You can make the mail merge as separate documents - one doesn't necessarily need to print them. The output can be sent to a multi page Word Doc.
However, every person has their own scenarios and needs, so absolutely do whatever helps with your own needs and workflow.
Cheers.
Very helpful! Trying to crack the code on loading an image now 🎉
Ty for the video
I am glad you like it!
Great video. Just seeing where I find the codes you mentioned
In the description, toward the end
This is a great breakdown and really helpful, can you help me understand how can I do this using google sheets excel? Can I use the same script for Google Excel?
Unfortunately the code for Excel is written in VBA and the code for Google sheets is Java Script, so it will not work.
Thanks Barb. Your solution is working just fine! Do you know how to populate fields on headers? It seems to work only if the fields are included in the main body of the Word Template.
I have never tried to populate the header!
Hi Barb, great video! Thank you so much - where would I find the code?
Scroll down to the bottom of the description
😍 You're a f*** genius!!! Thanks!
Glad it helped!
Impeccable work. Really helped. Is there a way for the code to also terminate every winword.exe that stays open in the backround?
I would be worried about closing it because it might close the process
Hey Barb, thanks for the video, i wonder if we can add pages instead and not save as a new word document over and over?
I have never tried to do that
With a few small edits to Barb's code (thanks to ChatGPT) I got it working after copying and pasting my template onto as many pages as I needed. You could probably automate the copying of the template into as many pages as you need, but I didn't bother for my short dataset.
Sub ReplaceText()
Dim wApp As Word.Application
Dim wdoc As Word.Document
Dim custN, path As String
Dim r As Long
Dim pageNum As Integer
r = 2
pageNum = 1
Set wApp = CreateObject("Word.Application")
wApp.Visible = True
Set wdoc = wApp.Documents.Open(Filename:="file path of word template", ReadOnly:=True)
Do While Sheet1.Cells(r, 1) ""
With wdoc
.Application.Selection.Find.Text = ""
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 1).Value
.Application.Selection.EndOf
.Application.Selection.Find.Text = ""
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 2).Value
.Application.Selection.EndOf
.Application.Selection.Find.Text = ""
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 3).Value
.Application.Selection.EndOf
.Application.Selection.Find.Text = ""
.Application.Selection.Find.Execute
.Application.Selection = Sheet1.Cells(r, 4).Value
.Application.Selection.EndOf
' Insert a new page
.Application.Selection.InsertNewPage
' Increment the page number
pageNum = pageNum + 1
End With
r = r + 1
Loop
' Save the final modified document
custN = "Progress_Report"
path = "file path to save document"
wdoc.SaveAs2 Filename:=path & custN & ".docx", _
FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
' Close the Word document and quit the application
wdoc.Close
wApp.Quit
' Release the object references
Set wdoc = Nothing
Set wApp = Nothing
End Sub
Dear Barb, Thank you very much for your tutorial! It is exactly what I was searching for. But If I have 500 rows in my excel file and today I need to populate templates, for example, from 23rd to 54th row, tomorrow I will have to populate another row range. My question is - what should be inserted into the code to proceed only part of the rows?
the easiest way you could do that is to copy the desired range to another sheet and run it off that sheet.
Dear Barb, thank you for the idea
Hey Barb! Excellent video. Really helpful. But I have a single field repeating atleast 10 times in the template. How to replace all of them in one shot?
you can install this code ActiveDocument.Bookmarks("\StartOfDoc").Select to move to the top of the document and then loop until all the fields are filled.
Great Video! Can I make this into one document only? I want to create each page on Word docx for each row in Excel.
I believe so
Hi Barb! When i click run, there does not appear to be any files created. I checked the file path and it's correct. I cannot figure out why it's not generating an error or docs
My advice is to "F8" through the code to see where it is getting hung up
@BarbHendersonconsulting thanks Barb! I adjusted the code so it reads (r, "A") and is now pulling all the correct data and generating the docs, however now I can't figure out why it is populating the top of my doc with the info instead of the square bracketed text as indicated in the code... 🤦♂️
Thank you, can we make all pages in one word file?
Watch this video for longer documents ruclips.net/video/o0ZyeMdnG9k/видео.html
@@BarbHendersonconsulting thank you but this is not what I want to do. I have a word template with one page and data in Excel. I want to do something like a mail merge and extract all the data to that word template
Hi Barb - great tutorial. My Q is I'd like to use the data input on excel in vertical format so headers would be in column A and the input would be in Column B - how would I adjust the code?
That is a big change, I will have to think about that one!
Hi there! For some reason, the Word documents the macro creates will only save to a mysterious 'template' folder I can't seem to find on my computer, even though I put in the path to save to a different folder. Any suggestions?
It really tried to put it in the templates folder, you might have to change it a couple of times
Thanks for this it was really useful. I used it to auto generate reports but as each line feeds down looking for the next field name how would you get it to check the header for one?
If you have all your fields in one row, it should not got to the next row looking for a field.
@@BarbHendersonconsulting Hi, don’t think I was very clear. In the word template I have a for document number but it’s in the header and appears to be missed. I have read a few threads and they appear to use headerfooterindex to search and add things to the headers?
@@therealbenriley I have never tried to put the field in a header! Good Luck
This has been EXTREMELY helpful! Thank you. I am looking to create if logic based on information within the cell - do you have any examples?
For example:
If the cell has "blocking issues: 1" write BLOCKED on the word template.
No, sorry. You would have to include an "if" function of some kind
Hey Barb! Great Video and very educational. I am having a 5152 Error popping and when I debug it - it points out
.SaveAs2 Filename:=path & custN, _
FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
The bug says that it's an invalid location but not sure what's wrong with the code as it's the same as yours.
Please help and thanks!
The code would work if you would like to save it to my computer! To determine the path go to the file folder where the file is and go to the menu bar and select copy path. Then you can replace my path with your path.
@@BarbHendersonconsulting I did put in my own pathway - This is what I have:
custN = Sheet1.Cells(r, 1).Value
path = "C:\Users\jared.bagnall\Desktop\ALPHA\"
.SaveAs2 Filename:=path & custN, _
FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
@@BarbHendersonconsulting Is it this that needs to change:
.SaveAs2 Filename:=path & custN
?
thanks barb but I'm getting 'complile erroe expected list separator' for the line
Do While Sheet1.Cells(r, 1) (not equal to) ""
replace (not equal to ) with I am not allowed to use those symbols in my description
Thanks for sharing, that’s brilliant
Glad you enjoyed it
What options are available, due to data security issues, to not use One Drive? Can I use just a local or private server file location for my Word macro/template?
I think you would have to download both to your personnel computer.
Macros do not run on networks.
Hi Barb, it is extremely helpful. Thank you so much for this. If Word document needs to be named "Mickey Mouse some place" (name+address), how should this be changed correctly?
custN = Sheet1.Cells(r, 1).Value
Appreciate your help.
That should do it!
@BarbHendersonconsulting "custN = Sheet1.Cells(r, 1).Value" creates document name of only one value (column 1). If document name must consist of column 1 and column 2 of each row, how this should be done?
I got this to work, then adjusted it to do a different sheet. On that sheet, it will only do a few rows (it changes how many each time) then it fails. It says the issue is on .Application.Selection.Find.Text = "" and gives me "Run-time error '462': The remote server machine does not exist or is unavailable." What would you suggest I try?
take a look at this one, it is for a longer list of data to word ruclips.net/video/o0ZyeMdnG9k/видео.html
Thank you!! Will do@@BarbHendersonconsulting
Hey Barb, how do you get number formatting to carry over? Excel Shows 1,000,000 however in Word it displays as 1000000. Thank you in advance!
That is a good question, let me work at it and get back to you
Is there a way to have a "Save As" window pop up when it is run, so the user can designate where the word doc is saved? I plan to use this macro for multiple people to use across multiple projects, so saving the document to the same file location would not make sense in my case.
I suppose that you could put in a pop-up window asking where you could save it.
Hey Barb excellent tutorial I got everything running great based off this and added a few line to add more data point! The problem I can't seem to solve is with taking the number formatted as accounting or currency in Excel but it displays as a generic number in Word. I've played (trial and error) with NumberFormat = but I can't seem to tackle this. Any suggestions on what I should research to resolve?
I did a quick search and came up with "Format(123123, "Currency")" .So I think that you would select the field and then apply that command. Good luck
Hey great video. I used your code and it works great but now I want to modify it so that it only applies to visible rows when I have a filter applied. Any suggestions?
Have you tried with a filtered list. I know when you send a table to outlook it only send the filtered values.
Hi Barb, I am still getting "run-time error:'424': Object required" when i add the command do while sheet1.cells(r,1) "". Any help/Tips/clearification?
you might not have all the references that you need. On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked
Thank you for the video! I have an issue where when I used the code to create the saved documents with the merge fields, the merged information is not in the correct location and the merge field names are still present (the info i wanted shows at top of page and merge field names are still in table). The preview in the template works fine, but the saved documents are incorrect. Any suggestions?
If the template works fine, try again
Hi! This is only pulling my first row of data (row 2) and not the entire sheet. Do you have advice on how to correct this?
Ensure that you have theses lines at the bottom
r = r + 1
Loop
if you have those f8 through your code and make sure that the r is increasing in the code.
@@BarbHendersonconsulting Thank you for the reply! I do have these lines at the bottom and my r is increasing in the code. It properly pulls the data from the first row and properly transfers it to the Word template, but then stops and throws the following error, Run-time error '9105': Application-defined or object-defined error.
What you could do is name your fields in Excel. For example Name=sheet1.cells(r,3).value
and then just before the r=r+1 you could refresh the values Name="".
Is it possible to save them as .PDF? If, so what do you reccomend as far as code?
My expertise is Excel not Word, this is my best guess ActiveDocument.ExportAsFixedFormat OutputFileName:= _
path & custn.pdf ExportFormat:=wdExportFormatPDF
@@BarbHendersonconsulting You may not have the expertise but you rock nevertheless. That code worked :)
@@ElectricSheep2199 I am happy it worked for you
Hi Barb, your video is a lifesaver, it is brilliant! However, i am having an error with this part ".SaveAs2 Filename:=path & custN, _
FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False", i am using Microsoft Office Professional LTC 2021...I cant seem to save, name the file due to fileformat...
I don't know what the solution would be, I am not familiar with Microsoft Office Professional LTC 2021
I am getting Compile Error: User-defined type not defined
on second line of code
wApp As Word.Application
On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked
@@BarbHendersonconsulting I'm getting the same error and I have all the correct references checked. Any other ideas for fixing that error?
@@kevinlong5864 You need You need Microsoft Word 16.0 Object Library too.
@@BarbHendersonconsulting I have all 4 checked but still have the same messge. How come?
I was getting the "5152" error because I was trying to create filenames from the value of a cell that contained characters that aren't allowed. Like colons. I replaced these with hyphens and it seemed to work.
good to hear!
Hi Barb! Great video! Very instructive and broken down and explained very carefully. When trying to run the macro, I seem to be getting a "Compile error: user-defined type not defined" for the the line of code reading "Do while Sheet1.Cells(r, 1) (not equl to)""....What could I be doing wrong. Need your guidance. Thanks.
I am not able to place symbols in the description of the video.
the command should read do while sheet1.cells(r,1) ""
@@BarbHendersonconsulting Hi Barb, I am still getting "run-time error:'424': Object required" when i add the command. Any help/Tips/clearification?
@@BarbHendersonconsulting Maybe post the code to a site like pastebin instead of in the description?
if i want to copy a excel chart in a especific position, how i can do that? because a chart its not a text and i dont know how to do it
I have never explored that
@@BarbHendersonconsulting today I finish the macro, its easy i use a word document with some especific words and the macro look for them and paste the text or charts from excel
Having difficulty doing it on a office 365 for mac.
I have never tried it on either 365 or on a Mac, sorry!
for me , the first time values correctly updated but if the same field repeats it does't update the value how can i resolve this issue
F8 through the code and make sure that the "r" value is actually increasing.
so it's a mail merge essentially?
like mail merge but with VBA
Hi Barb I am getting a compile error User-defined type not defined
two reasons might be:
1.I am not able to place symbols in the description of the video.
the command should read do while sheet1.cells(r,1) ""
2.On the visual basic window -tools -references; I have Visual basic for Application, Microsoft Excel 16.0 Object Library, OLE Automation and Microsoft Office 16.0 Object library checked
You need Microsoft Word 16.0 Object Library too.
@@shipcore6757and then what to use as a object? Because its required now
Thank you Barb, great video!
1) I am experiencing an issue that, it write the data from sheet directly in one line like "Donald Duck11112Vancuver..." Do you have idea why?
2) would it be possible to first find every occurrence of in dotx, replace, and just after that move to another cell (f.e. )? How would you write it?
Thank you
1) I think that it has to do with the formatting on your word document.
it replaces the field exactly were it is found.
2)The way I do it does not allow the software to overwite by accident.
even i am facing the same issue. Instead of finding the and in the word document, it just displays continuously in the beginning "Donald Duck11112Vancuver" in one line. Let me know if you have any solution.
How do I retain formatting of the template?
the line
Set wdoc = wApp.Documents.Open(Filename:="C:\Users\screa\OneDrive
econstructionBH\Documents\Excel\welcome.dotx", ReadOnly:=True)
The read only prevents the word template from being changed
What the heck happened at 3:37?
Nothing! before explaining the code I needed to show you what references are required for the code to run!