Create New File With Data From Database Through A Template
Sep 12, 2006
I've been looking around to find a solution for my problem and as a last resort I have decided to make a post, and I will get straight to the point. I have 3 xl Files of relevance:
1 - is my "database" which consists of multiple sheets with different information in each. Each sheet is correlated with each other by one common ID. 2 - is my "template" which is 2 sheets, which functions as a report. The template serves as the Report which will be printed. The data from each row from the database can fill the spaces in the template.
3 - "the tool" xl file is where i have my macros and the mapping for the which columns from the database belong to which cells in the template.
Basically what I'm trying to get to work is: User opens Tool clicks "create Reports" and the tool should then open the database, pick the range of rows from the database put the data into the relevant cell in the template save the "template" with the info on it, close it and do it again with each row of data from the database. So if I pick rows 4 to 34 in my tool, it should create 30 xls files from the template and fill in the data from each row into each newly generated "report".
View 4 Replies
ADVERTISEMENT
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
May 6, 2014
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx‎
Template.xlsx‎
View 9 Replies
View Related
Jul 22, 2014
I already transfer my data from database (excel.xlsm) to an old worksheet (excel.xlsx) with a table by using VBA. Now I want to make this old worksheet become a new file with new file name like yyyy/mm/dd/where. Is that possible?
View 4 Replies
View Related
Mar 8, 2014
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
View 1 Replies
View Related
May 31, 2007
I need to read data for a group of charts into a master template file.
Sometimes my chart data range needs to be
AS31:AT35
Other times, when different data is read in, the same chart may need to only read
AS31:AT33
Is there any to name a range to read until row AT reaches it's first blank?
View 2 Replies
View Related
Aug 4, 2006
Currently I have a database in an Excel template. After a user creates a new workbook from the template, a macro button on sheet 1 brings up a window (a userform) to allow selection of items from the database sheet. The item selected is entered on sheet 1 by means of VLookup. That works fine, but to edit the database correctly it is necessary to open the template itself. This is not a user-friendly method.
I figure the best way to solve this dilemma is to separate the template from the database. That is, make the database sheet into a workbook. This I've done. Here's the problem: What code is there that links the list box in the userform (of the template) with the closed database workbook? Is it even possible? If a file path type code is required, it may work on my computer, but when I use my template and database on another computer, the code doesn't work.
Another problem, and similar is this: I'd like to be able to have a macro button that opens the database from the new workbook (which was created from the template) in order to edit the database. If having a template seems to be my problem, I must have it since each workbook created from it is a different project.
View 3 Replies
View Related
Mar 18, 2013
I have a workbook that has a total of 128 pages. The problem is that a lot of those pages won't be used. Basically I have a sheet that needs to be filled out for each day up to around 30 days, sometimes more, most times less.
Currently I have created all the sheets that I will typically need (31 daily's, 31 daily printable reports).
Data is entered into the daily (and other spots) and then with the use of formulas transferred to the report sheet which is hidden and then printable with the use of macros.
I also need to withdraw some of this information (CSV File) to be able to populate a database.
Is it possible to have my "daily" page as a template and then create the subsequent pages as I need them? (this would have to be done with a bunch of other report pages as well). For ease of use I would need all the formulas etc that I currently have to be able to be "created" as well. As an example, there is a running total of costs associated with the "daily" pages that would need to be carried forward.
View 8 Replies
View Related
Jul 1, 2014
I am creating a macro to create a new sheet that is a template that has already been created. I want to create a variable that references this new sheet so I can use it in a sum function.
Here is what I have:
Sub TotalSum()
'
' TotalSum Macro
Sheets("Sheet1").Select
Sheets.Add Type:="C:Users cweberDesktopNCR & NDE TEMPLATE.xltx"
'
Dim WS As Worksheet
WS = ActiveSheet.Select -------> I want to reference WS as this newly created template.
View 7 Replies
View Related
Aug 25, 2008
Well I am here at this fork in the road again.
I need to create 3 new tabs... 10,4, and 1
I have tried this and it creates the worksheet, but does not copy the "sheet1" as its format.
I need to do this 3 times.
For i = 1 To 1
Set ws = Worksheets.Add
ws.Name = "10"
Worksheets("Sheet1").UsedRange.Copy wsnew.Range("a1")
Next i
View 9 Replies
View Related
Feb 7, 2008
I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
View 9 Replies
View Related
Mar 22, 2012
I'm looking to create a dynamic invoice that has one main invoice worksheet that references a worksheet with client billing information. Keep in mind that line items will differ between clients, and ideally I'd want to be able to:
Have date autopopulate, included in every line item Select a client, which populates billing info as well as the invoice # nomenclature specific to that client Be able to have client line items specific to the client populate based on the dropdown I select
Overall, no real specific guidelines, just trying to pick some other people's brains about how to approach a dynamic invoicing template. To preface, we have about 60 clients.
View 7 Replies
View Related
Feb 9, 2014
I have a master template with columns running from A to Q. Most of what she wanted is fairly simple 'if' statements, however she also wants to move the contents of one cell to an adjacent sell based on the number exceeding a certain value,
[Code].....
My problems start when I want to create a new sheet/tab and let her name it. I would have simply created 12 months for her, unfortunately there is a sheet for a number of people and the start of the year can vary. My simple solution was to create a macro that copies the original Template (effectively sheet 1) and puts it into the newly created tab. However this does not copy the above VBA code across
[Code] .....
View 1 Replies
View Related
Apr 28, 2014
I have a template document, created in MS Word. I want to generate, from Excel, a new document as would happen when you open the template from windows explorer or whatever ie. Template1.doc as opposed to Template.xlt.
The best I've managed to achieve is the opening of the template.
View 6 Replies
View Related
Jun 17, 2008
I want to create a standard template used for all commodities. To do this I have several columns that need to be on every quote and then depending on the commodity, a few other columns that need to be added in. I want to automate this so that I have a heading "Commodity" at the top and then a drop down menu with the different options (plastic, glass, etc.). Then once the commodity is selected the necessary columns will be inserted into the template. Is this at all possible using macros or anything else?
View 9 Replies
View Related
Jun 18, 2008
I'm trying to create a macro that creates new worksheets with buttons on them that have macros attached. I've been able to produce one worksheet with a button and macro, but when the function that copies the code is looped it crashes out of Excel.
I realise that the usual method of doing this would be by templates, but this code will be run on other people's computers so the template wouldn’t exist in the location that i had specified (I think with my limited experience with VBA - Please correct me if I'm wrong). I've also tried to copy the page with the button on, but this takes too long (even with screenupdating = false) as there are many sheets to be created.
This is a stripped down version of the code that I have created for the purpose of this forum. To recap, the function that creates a new page, with button, with macro attached works. It's when it's looped that it crashes out. I've stepped through the program, and it reaches the second message box, then it gives up on life ..........
View 9 Replies
View Related
Nov 10, 2011
I'm trying to create a workbook with multiple worksheets. My first sheet to be named "Main" and each additional sheet to be named in sequence after the values in the cells between B5:B98 on !Main.
Example:
B5 = "01"
B6 = "01.1"
B7 = "01.2"
B8 = "02"
B9 = "03"
etc...
I'd like to have a macro that would take a worksheet in this workbook named "Template" and make a copy for each cell between B5:B98 and name that new worksheet "c" and then the value in each of those (B5:B98) cells.
Example:
First worksheet named "Main"
Second worksheet named "c01"
Third worksheet named "c01.1"
and so on.....
View 1 Replies
View Related
Jan 21, 2014
I have been working on the following VBA code, which should copy specific charts from excel into specific places in a word-template.
I Am quite new with VBA, so I googled the code, changed a few things, and it works exactly as it should - except from one thing. When the Word-document is loaded from the template, I would like the document to be "saved as..."
Instead of just opening the template. I have tried
Code:
.ActiveDocument.SaveAs Filename:=fname & ".doc"
The code is as follows:
Option Explicit
Sub EksporterTilWord()
Dim appWrd As Object
Dim objDoc As Object
Dim FilePath As String
Dim FileName As String
[Code] ...........
View 1 Replies
View Related
Nov 15, 2007
I have 8 different workbooks which is 100% similar. Only values registred on each sheet is different. the format and rows/columns are the same.
The sheets is something that changes, when I want to improve layout etc.
I have one Template book where I do the changes and try to export this to the other books (call it upgrades). For macro moduls it works nice, just replace.
The problem is the sheets. When I have changed a sheet, replace it, it would not adopt values from the other sheets.
Ex. In sheet 2 I have named the cell 'B60' for U_KU in the new sheet 1 I expect at using =U_KU in cell I7 would adopt the values from B60 in sheet 2. But nope.
I can see that there is an link to the template workbook in edit ->linkage. Would this cause problem?
View 2 Replies
View Related
Sep 17, 2012
I have been working on a template using validation tool so as to create drop down lists. Now I have to select data from a combination of two fields.
Example: I need info from Dataset A and Dataset B to create a new drop down list from Dataset C.
I tried with If AND formula =IF(AND(B4="Test";B6="de10");"ok";"no") where due to the selected values I got Ok as a return answer. However I need to have a dropdown list of relevant values selected and I am having some issues.
When I name my group (Dataset C) as DE10 so that it can be linked to DE10 value (dataset B) so that I can do a validation then I get an error as DE10 is an excel field name; therefore I thought of the IF AND solution as I was thinking of doing so that I can name Dataset C as XYZ (instead of DE10) on the data sheet however I am having some trouble in getting the drop down list to work.
Basically I need a drop down list from dataset C when certain info is picked from Dataset A and Dataset B.
View 1 Replies
View Related
Feb 10, 2013
I had created a base data with evaluator and performance appraisals templates which should be sent to each evaluator for filling up the forms based on templates
Next,
Macro that would i require will be
Where in my base data if i select filter of "Arun", i will performance appraisal templates in column I. Next, i have sheets individually created templates which refer column I
So, based on arun filter if i select, it should create a new workbook for arun and six individual sheets of employees reporting to him with a base data and appraisal templates similarly if i select anita in filter , it should create a new workbook for anita and six individual sheets of employees reporting to her with a base data and appraisal templates
View 9 Replies
View Related
Nov 5, 2004
Is there any way to create a default template that will apply all the same formatting, print options, etc to every new workbook that I create. I guess what I am asking is a way to change the excel defaults for text type, border size/color, etc.
View 4 Replies
View Related
Nov 12, 2007
I need a little help creating the macro for a template file which is going to be used for data input and further analysis. I have a set of arrays, which can be altered. The arrays may look like so:
View 9 Replies
View Related
Jan 7, 2007
I have a file with two sheets - Main and Reference. The file has a macro that when used from Main sheet displays some data (images to be specific) from the Reference sheet. The main sheet is of a specific format and has some buttons to call the macro.
I want to have the capability to create any number of main sheets of the same format. I know this can be done by saving the Main sheet as a template and right clicking on a sheet and clicking 'Insert..'. But that would require the template to be stored in the excel start file on the local computer.
I want to know if there is any way the worksheet template can be stored within an excel file, so that if the file is sent to people on a different computer, the user can easily add more worksheets using the template?
View 9 Replies
View Related
Aug 3, 2013
I am creating a database for Customers and Invoices.
1.I want to select a customer from a drop down list. I only have a few customers.
2.I want to save Invoices to review or make changes before mailing.
Per sample Invoice template.
View 4 Replies
View Related
May 16, 2008
I am trying to create a 'Database' of Project Managers where by a skill can be entered into a cell and the VLookup will lookup the Project Manager that has this skill. There is the posibility that there will be more than one Project Manager with the same skill, formula so that the Lookup can return multiple values
the formula i am currently using is; ...
View 9 Replies
View Related
May 26, 2008
I am seeking assistance in creating a 'database' type function within EXCEL workbook. I need to create a record of all job applications within our area and only a handful of staff have access let alone know how to use access. All are able to use spreadsheet/workbooks. Would like to be able to have entry page (i can do this fine) but I would like a search funtion that will allow staff to be able to track the progress of applications and add comments etc
View 3 Replies
View Related
Jan 7, 2010
how do i go about saving an Excel sheet (template) automatically, using data stored in the A1 cell of Sheet1 as the file name. i want to use an Excel template to capture data, and then store the data in a central location. to do this i am thinking of getting the template to automatically save to a set location e.g. C/: files. is there a simple way to do this.
View 2 Replies
View Related
Sep 18, 2013
I am trying to create a cross-reference database in excel. I am not sure if I am using the correct terminology, so here is what I need to do:
I have three columns that contain text cells, each with different numbers of rows. Now imagine that cell A1 is related to cell B3 and also to cell C5. There are numerous different combinations similar to this. Is there a way to show this relationship between cells in excel. If so, how?
View 1 Replies
View Related
Sep 19, 2013
My situation is as follows:
1. I have one sheet with all the information about my customers, such as customer name, address, mobile number and email. I have also added customer reference no...... this could act like a primary key in databases...... This is SHEET 1.
2. my other sheet, lets call it SHEET 2.... is basically a template of my quotation where at the top I need to enter the customer details such as customer name, address, mobile number and email.. Sometimes I have returning customers and I need to enter their details again and again.... it is time consuming.
Initially I would like to enter all my customers detail into SHEET 1.
When I have to create a quotation in SHEET 2, I would like to enter a customer reference number and it should bring all the data from SHEET 1 about that customer and place it into SHEET 2 (quotation template)...
View 3 Replies
View Related
