I am wondering if anyone as created a what to do word art, using a input variable.I.e in may example I have a person, Suzie Smith who is an Chief Manager, when I box requires her signature, I would like for it eo be imprinted going diagonal across the box. Is this possible?
View 7 RepliesWhen I create a document from Excel to word from a sheet in Excel the Sheet needs to be unhidden. How can I Create this report if I want to "hide" this sheet.
View 7 Replies View RelatedI am working on a project where I am creating analysis using a subroutine in excel/vba and I would like to have the subroutine generate a report that is a word document. I have never used excel/vba to create and modify a word document and don't know how to approach it. I imagine that I will have a template.doc that I can access and save as another file. Does anyone have an example of creating and modify a word document in this manner.
View 2 Replies View RelatedI've created an excel document to count calories. Here's an example of what I've made...
Item
Quantity
Servings
Cal
Fat
Carbs
Protein
Milk
8 oz
1
150
8
11
8
[Code] .....
MACROS
31.4
41.1
27.4
So basically I'm wondering if there's a way I could simply type in "milk" and have the rest of the information appear (cal, fat, carbs, etc). What would be even better is if I could somehow enter the item and create a formula to multiply by the serving size. I feel a word bank with a list of common food items I eat throughout the day and there respected numbers would be the most simplistic way.
The following code is used to export values form Excel into a word document:
The report obtained then looks like this:
Excelforum.jpg
How can I make the values fit within the margins of the word document?
I have a bunch of data about companies in an Excel workbook. I need to be able to create a "summary document" about a selected company in word. One of the items that needs to be in this summary is a pie chart.
This code works fine for generating the basic text of the document:
Code:
Sub export_to_word()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim companyName As String
Dim address As String
Dim revenue As String
[code]....
Creating the chart has been more problematic. I adapted the following code from an example found on MSDN:
Code:
Dim valueChart As Chart
Dim chartWorkSheet As Excel.Worksheet
Set valueChart = wrdDoc.Shapes.AddChart.Chart
valueChart.ChartType = xl3DPie
Set chartWorkSheet = valueChart.ChartData.Workbook.Worksheets(1)
chartWorkSheet.ListObjects("Table1").Resize chartWorkSheet.Range("A1:B12")
[code]....
What I've found though is that this code is based on a macro inside the word document, not excel. So it runs fine from within word, but within excel it throws an error at this line
Code:
Set chartWorkSheet = valueChart.ChartData.Workbook.Worksheets(1)
because ChartData is not in the object model in Excel. how to proceed at this point.
I have a huge Excel spreadsheet with various worksheets and data, I want to merge some of that data into a report using word template. How can I pull simple data like the company infomation into that word report from Excel? Do I need to know VBA to accomplish this?
View 1 Replies View RelatedThe way that I was thinking about doing this would be by creating a template in word and using bookmarks. Then I would use VBA to populate those bookmarks based on the person (publisher in this case) i was invoicing.
I'm having a couple of issues logistically though. I'm not sure how to attach the sample invoice, so i'll do my best to write them out and if i figure out how to attach a doc by the end of this i will include a sample so you see what I'm talking about.
Issue 1: Each Invoice form has the publishers Name and Address listed at the top of the form. The problem is, the information i'm pulling the number of sales (and money we owe them) is from a different spreadsheet every month and wouldn't contain this address information on it as it is an aggregate spreadsheet of ALL publishers numbers. Would it be possible to create an initial template for each publisher and then have the rest of the information be populated with the data from the CSV that has their sale information. I could create a seperate database in excel with each publishers address and name but then it might get difficult to match these up. how to automate that, as we have 100's of publishers and invoices are done every month.
Issue 2: Bookmarks seem like a nice way to use VBA to replace their values with excel data (in theory, i've only read about it and never attemped)... However, in this particular invoice form, I have to create a seperate listing in the description field for each product a publisher sold (including how many they sold and how much money they are owed). Some publishers may have sold 5-10 different products, while some may have only sold 1 or 2. How do i get word to insert a new bookmark (or something) based on the number of products a person has sold.
I'm working on a national survey with valuable data from students. The plan is to turn around, 100 different reports to the departments with their respective students.
The issue that I have is creating multiple tables from multiple sheets in excel into a word table, any way of automating it.
What the tables look like in excel:
What I want them to look like in word, with a chart if possible:
I realize that I can just copy and paste easy table into word from excel and with a few clicks of a button, get what I want. But i'm looking for something to automate and simplify this process. There are about 300-400 variables that I need to crosstab with certain demographics.
If it matters any, I bought a software package (Q Market Research) but it doesn't make the tables look exactly how I want them to look. The data is from SPSS, those tables are flat out.
It is possible to define a variable in a MS Word macro and send it to an Excel spreadsheet?
View 9 Replies View RelatedColumn A will be completely empty.
When an * is inserted into a single cell in column A, that Row number will become the variable in this equation:
=Sheet1!$G$'variable'
i.e. if i put a * in A14, then the formula should read (=Sheet1!$G$14).
How would I create a variable range with:
Sheets("output").Cells(I + r, 1):Sheets("output").Cells(I + r, 9)
I currently have the below macro:
If Cells(1, 3) = 2 Then
For a = 1 To 336
b = Round(a * 2 + 0.99)
Cells(b + 0, 3) = a
Cells(b + 1, 3) = a
Next a
MsgBox " Cell C1 is not equal to 2"
End If
where the intent is that whatever cell A1 is equal to, that is how many times over it will duplicate a number in consecutive cells before moving to the next number, ie:
1
1
2
2
3
3
etc to 336.
How can I tweak the code so that if I change the data in cell A1 to 9, I don't have to go in and change the macro?
I have never used VBA in word before, so I am assuming I am making it more complicated than it needs to be. I have a macro that stores 20 excel values as variables, then opens up a word document. I want to replace values in word with my values in excel. I keep getting an error with the replace part. I do not want to use bookmarks or mail merge. MSWord is a reference in excel VBA.
Code that stores excel variables (Example: Var1=Data1 and Var2=Data2)...
'Opening up the correct word document
Dim WdApp As Object, WdDoc As Object
Set WdApp = CreateObject("Word.Application")
WdApp.documents.Open ThisWorkbook.Path & "" & Range("D1").Value & ".doc"
WdApp.Visible = True
[Code] ..........
I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:
='F:Projects[CustomerDB.xls]Sheet1'!VariableCellGoesHere
I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:
='F:Projects[ProjectDB.xls]Sheet1'!$A$355
='F:Projects[ProjectDB.xls]Sheet1'!$B$355
='F:Projects[ProjectDB.xls]Sheet1'!$C$355
Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?
I am trying to create a matrix in VBA with variable matrix size. The matrix I need will be anywhere from 3 to 9 columns and 15 to 30 rows. I want to set the size as variables and then create the Matrix using these variables, similar to:
rows = 4
columns = 25
Dim XMatrix(rows, columns) As Variant
I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
View 1 Replies View RelatedI'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.
Code:
MyWordVar = ActiveDocument.ContentControls(1).Range.Text
).
What I can't figure out is how to pass the value of "MyWordVar" to a variable in Excel.
I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
how do we do something like Range("A1:B2") using an index for the cells. For example Range(R[0]C[0]:R[i]C[j]) which doesn't work. I know about Cells(i,j), but that only returns a range object of a single cell.
View 9 Replies View RelatedI have a spreadsheet that I have variable data in and I want to transfer specific parts of the workbook into a Word doc.
Not a problem with the Excel part, I can select each and every cell that I want, copy them and then...
Recording a macro hasn't done the trick as the code I have only copies and doesn't paste. I know I can edit the code to open Word but the problem is that when in Word, the data needs to go to very specific places, some of it goes in the header, most goes in the main body, I can't see how this could be done as there are no cell refs as in Excel!
is it possible to paste to say halfway in a sentence, in a macro?
Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?
For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.
I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.
If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.
Code to return records on the date:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate
This returns data, however if I slightly amend it to get records on or after the date:
Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate
It returns nothing. Is there a syntax problem in my code.
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
View 1 Replies View RelatedI have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
5
Test
8am-2pm
Test
5pm-10pm
How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.
View 13 Replies View RelatedI need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word