Creating Multiple Word Tables From Excel Data?
Sep 17, 2011
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.
View 9 Replies
ADVERTISEMENT
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Jun 5, 2014
I am trying to take multiple tables from a Word document and import them into an Excel worksheet. Currently I have found two versions that when combined, could yield what I am looking for. The first one imports the table's data from Word, but does not maintain formatting of the table (font, colors, rows/columns etc.):
The next code maintains formatting, but only imports/pastes one table:
[Code] .........
For the second one, I do not like the fact that it is calling a specific Workbook to paste into. If I could somehow maintain the ability to import/past multiple tables while keeping formatting that would be perfect. An extra bonus would be to import each table within the Word document into individual Worksheets in Excel. I am also using Office 2010.
References: [URL] .........
VBA - How to preserve source formatting while copying data from word table to excel sheet using VB macro? - Stack Overflow
View 14 Replies
View Related
Mar 22, 2013
how to do this, but I know it can be done.... I want to use one drop down list created via the Data Validation 'List' Criteria to then lookup data based on the selection made in this list...... this will require a number of reiterations to get to the final result......
So:
Drop Down No:
1. List: Department Function:
2. List: Sub Function List Based on Selection from 1.
3. List: Job Code, Title and Pay Grade based on Selection from step 2
how to ensure that we are able to minimise the overall workbook size due to the complexity that is required here as this is just the basics.... this will need to applicable further once this basic requirement has been fulfilled.
View 9 Replies
View Related
Jan 25, 2010
Currently I am going back and forth between an excel document and a word document cutting and pasting values from the excel spreadsheet into tables in word. The task is a regular occurance therefore I wish to create a Macro that can automate this procedure. Both the excel and word documents are fixed templates therefore once a Macro is created it can be applied to all future work of similar nature.
Each table in the word document contains 6 rows of values in a single column. The excel data is arranged in a table that is 6 rows by x number of columns (how ever many sets of values there are for the particular job) therefore x determines how many tables must exist in the word document. I move between the excel and word document cutting and pasting each column into each table. This is not so time consuming if x=10 however on occasion x=100+ and it does take time.
View 8 Replies
View Related
Dec 11, 2013
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 Related
Apr 7, 2010
The 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.
View 3 Replies
View Related
Jul 11, 2008
I have an Excel spreadsheet of data that I would like to export into tables in a new word document.
The code below (without the marked section) does this, but I can’t figure out how to create a second table after the first one.
Eventually I will use a For…Next loop to do this many times, but I’m trying to make the problem smaller by just making a second table.
Sub CreateNewWordDoc()
' add a reference to the Word-library
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdTable As Word.Table
Dim xText
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add ' create a new document
Set wrdRange = wrdDoc.Range
wrdApp.Visible = True.........
View 9 Replies
View Related
Apr 1, 2009
I am having a lot of trouble with pivot tables in Excel 2007 VBA. I am trying to create pivot tables using macros (connected to buttons the user can press to create the pivot table) - please don't ask why, but i need to do this!!!
I used the record fuction in excel 2007 to produce macro code which will produce the required pivot tables when the user presses a button.
Unfortuanately the coding seems to work fine when i have one pivot table in a file but breaks down if i record code to produce another pivot table.
I have attached some code below (which was produced by the record function) and is intended to produce 2 seperate pivot tables (the macro submacro2 produces the 1st pivot table and the macro submacro4 lower down the page produces the 2nd pivot table). I have also indicated the point in sub 4 where the code breaks down - basically submacro4 just doesnt run!
View 14 Replies
View Related
Jan 8, 2013
I am creating many tables in excel using a macro, and I want to copy them into a word document for later use. All the tables come from a long list, so for simplicity, they all get created on the same range starting at F1. Before deleting the existing table and adding the new one, I'm trying to copy/cut the table and paste it into a word document.
In case it makes a difference, I have office 2007
VB:
Sub ExcelToWord(LastRow)
Dim objWord As Word.Application
Range("F1:F" & LastRow).Copy
With objWord
.Documents.Add
.Selection.Paste
.Visible = True
End With
End Sub
View 6 Replies
View Related
Sep 13, 2013
I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010
Here is a copy of what I have
Code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _
, _
"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, _
";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _
, _
"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _
[code].....
Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.
View 1 Replies
View Related
Jul 17, 2013
I have several word Files which has standard letter format and contains some tables which needs to be extracted / imported to Excel sheet
The File name is "employee name" which also needs to be extracted , if possible in same excel file.
Attached is the sample of word file. There are 3 tables which needs to be extracted
I have several files and I want to avoid doing it manually.
View 2 Replies
View Related
Apr 16, 2012
How to write a code to import all tables from a single word document into a single excel sheet? (we don't know exactly how many tables there will be,or how many rows in a table there will be , but the columns are certain, which is 9, from A to I.
View 4 Replies
View Related
Jun 28, 2013
I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.
My department processes payments, both for internal clients and external clients. My Payments table looks like:
Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity
Additionally, I have a table for Mail Opening, which looks like:
Date | Employee | Operation | Quantity
My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.
View 3 Replies
View Related
Jun 2, 2014
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?
View 14 Replies
View Related
May 2, 2013
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.
View 1 Replies
View Related
Jan 10, 2014
My family owns a Fast Casual Middle Eastern/ Mediterranean Restaurant. I'm taking charge of making it as profitable as can be! Our plates are different combinations of kebabs, rice, Falafels, Gyros, salad, platters. So theirs tons of combinations.
I am Dead serious about knowing REAL plate costs:
I spent days entering price data, weighing meat, produce, calculating usable yields. I then built "component builders" using data validation drop-downs, and Index + Match functions to construct the costs of the recipe items,standard base items and side items. So I now know How much a skewer of Marinated Kebab Costs me, and all the Components that go into building a plate.
I've now built a "Plate Builder" (See Pic) to create the EVERY combination available at our restaurant. So when I'm done filling in all the builders, I'll have a whole lot of tables. I'd like to pull the name and plate cost from each table and create a big table organized by whether its eat in or take out. How do I do it.
TL;DR: Need to create a table that pulls data from several other tables....
View 2 Replies
View Related
Aug 12, 2006
I've attached what I've done so far. I'm trying to figure out a way to capture everything. For instance, instead of using separate boxes for the different groups, isn't there a way that I can put the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, and list the different units, and different groups. I'd like it to be sort of like the very last box where I have the different sessions and the different groups in there, but I'm not sure where I could add the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, because the last box only formulates the percent attendance for each session by the different groups. I need it to also formulate the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend. Is there a way this can be done without using separate boxes for each group.
View 3 Replies
View Related
Feb 7, 2013
I'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.
View 1 Replies
View Related
Apr 22, 2014
I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.futwiz.com/en/players", Destination:=Range("$A$1"))
.Name = "players_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
[Code] ........
On the second page the URL changes to;
URL;FIFA 14 Ultimate Team Players - FUTWIZ
and the .Name field changes to;
.Name = "players?page=1_1"
and on the 3rd page I get;
URL;FIFA 14 Ultimate Team Players - FUTWIZ
and..
.Name = "players?page=2"
View 3 Replies
View Related
Jul 31, 2006
I want to a macro or a function that can search for the value which corresponds to a cell and copy it to another cell. please refer to the attachment for more detail.
View 9 Replies
View Related
May 7, 2014
I need to import a long list of word docs into one excel sheet.
I think my situation is unique in that I need the word document title in column A and the data inside the word document in column B through D.
Also each document needs to be in its own row as well so we can organize it later.
View 4 Replies
View Related
Dec 22, 2013
I have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
My NPV is the result of a series of calculations to ultimately arrive at a cash flow figure and then apply the WACC (average cost of capital)
I understand how to use data tables But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV sensitivity
My sheet and calculations are all set up
I've done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
View 1 Replies
View Related
Dec 9, 2011
I am trying to plot multiple tables on the same line chart in Excel 2007. I have a list of X and Y values for Table 1, Table 2, Table 3 etc.
However the size of the tables are not the same for each table. For example, Table 1 might have 200 X and Y values, Table 2 might have 175 X and Y values, Table 3 might have 105 X and Y values, etc.
So when I plot these data tables on one line chart the individual plots are off center. They should all be centered at 0.
View 1 Replies
View Related
Jan 29, 2012
I have one sheet with 25 tables in it, And i want to extract the whole row from this sheet to another sheet based on values ( Office1, Office2, Office3, Office4 ) if appeared in thrid column "C".
- the 25 tables created on the 1st sheet to be in the other sheets as formatted as well.
- once "Office1" appeared in the original sheet in the 1st table, Action : copy the whole row to sheet named "Office1"at the top row in the first table.
- once "office1" appeared again in the original sheet in the 1st table, Action: copy the whole row to sheet named "Office1"at the 2nd top row in the first table.
- once "office1" appeared again in the original sheet in the 2nd table, Action: copy the whole row to sheet named "Office1"at the top row in the 2nd table.
- and so on, the same with other 3 remains sheet.
View 5 Replies
View Related
Dec 12, 2013
I need to insert a blank row between each row of data under my headers (not the immediate row) until I reach the end of the particular table. I then move down to the next header and repeat the process.
My starting data looks like this:
HEADER
DATAROW1
DATAROW2
DATAROW3
-emptyrow-
HEADER
DATAROW1
DATAROW2
DATAROW3
I need it to look like this:
HEADER
DATAROW1
-emptyrow-
DATAROW2
-emptyrow-
DATAROW3
-emptyrow-
[Code] .....
View 3 Replies
View Related
Oct 17, 2006
I need to know how to open and manipulate a Microsoft Office document using code in Excel.
I have a list of Names in an Excel column that I am trying to paste into a Word table (formatted as Avery labels). Each name goes in a different table cell in Word. Example: Copy from "B5" in Excel and Paste into the first cell in Word, copy from "B6" in Excel and Paste into the second cell in Word, etc.
I know the code needed within Excel and I also know the code in word used to setup the table format. I just need to know how to "call" it from within Excel and how to copy/paste back and forth.
View 9 Replies
View Related
May 2, 2013
I have multiple pivot tables with different data source. I wish to have one slicer which control all the pivot tables. I would have one common colum for all the pivot tables which is the one i wish to control for instance the person in charge. Note that all my pivot tables are from different data source. how to do it?
View 1 Replies
View Related
Dec 16, 2013
The spreadsheet is comprised of 4 sales data sheets, one for each store. Each of these sales data sheets runs several pivot tables (One for dept sales, one for waste, one for sales on Selected UPCs etc) and I used to have 1 copy of each pivot table for each store (ie 24 pivot tables). Now I have found a way to have only 1 copy of each PivotTable which changes based on which store is selected using the following code:
Code:
Sheet13.PivotTables ("Top10LW").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)
This works great however I was repeating it for every pivot table I had in the spreadsheet, causing messy code and file corruptions.
Is there any code which I can use for updating all pivot tables, using as minimal code as possible, all in one hit?
View 3 Replies
View Related
Sep 20, 2009
I am trying to combine sorted data from 2 worksheets to a new work sheet to process further. I have one worksheet with order number, part number, order qty but with different delivery dates. On another worksheet, I have the order number too, part number, the qty delivered. The qty delivered is not always the same each delivery.
My aim is to find out how many are already delivered under a certain purchase order and the balance of undelivered parts.
I used pivot tables to sort out the data but I am stuck here not knowing how to extra the sorted data from the pivot tables to the 3rd worksheet. I will need to match the order number and the part number.
View 9 Replies
View Related
