Hello everyone,
I am at the end of finishing my first project in Excel/VBA. My manager wants me to document the complete project. I really don't know how to do that.
So Just wanted to know if there is any predefined Template.
Is there any Software / Excel Add-in to provide VBA Project Documentation? I'm looking for something that can tell me which code does what? Which codes and functions are related. What are the references (with Other MS Office products / foreign-objects if any). How many codes and code names
View 8 Replies View RelatedI 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.
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] ...........
I have a word document that is a rental agreement. Im trying to make an easy to use user form where a person can input the information and it populates in the correct areas and saves as a pdf. How I could put the word document into excel and have a userform populate designated areas.
View 1 Replies View RelatedAll I want is to generate a word document with built in word header and footer (header and footer style name is "alphabet") based on the values which i mention in excel(path,word file name and header content.
View 1 Replies View RelatedI have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END
[code].....
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.
View 9 Replies View Relatedhow to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies View RelatedI need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies View RelatedI am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.
I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.
Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.
Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.
Tracker has
columns
A - Name
B - Department
C - Date
D - Docket No.
C - Total price for the docket (calculation of E to Z)
E to Z - all smaller entries
Invoice has
columns
A - Date
B - Docket No.
C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.
Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.
I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.
Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.
I need a script that will turn a excel doc into a txt doc. Thats the easy part. The hard part (at least I think it is), is I need it to be in a certain format and I'll do my best to explain that fomat below.
View 9 Replies View RelatedI Have a number(30+) of excel files based off of one template. I have since updated the template, thus making the the old ones obsolete. Is there a way to update the older files to use the new template without having to copy and paste the addition's row by row column by column?
None of the additions to the template will change the placement of existing data, nor will it change the data itself. It will however add Values to cells that were previously empty.
Hints, Tricks, or Ideas?
1)
I created a one-sheet template, and a new workbook from that sheet. When in that new workbook I go Insert>Sheet>Other and select the aforementioned one-sheet template, Excel crashes, or if not, it adds the new sheet, but then no longer saves the workbook and starts producing error messages (like: "An unexpected error has ocurred. AutoRecover has been disabled for this session of Excel.").
1a)
Now, the complication is that this is working when I do a model operation with a generic template sheet. So I checked the template that I actually want to form sheets after, and Excel finds no errors, nor does it's name contain any unusual characters. What could Excel prevent from working with a template like this?
2)
How do I edit a template? The only way I can find is to manually find the spot in finder, open, and save with the same name. But: if I do that, the documents basing on that template don't change accordingly. If they're intended not to, the whole template procedure makes no sense. I could then just as well copy a file. — I've been searching for tutorials on that, but google doesn't even return a single result on Excel "edit template". Therefor my very basic question here.
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‎
I have a list of invoice #'s on a sheet named "Temp Sheet".
I have a VBA macro that created a new tab for each entry and named it the invoice #. So basically the vba code created a new tab ( based on the number of invoice #'s on my list ), and named each tab an invoice number. So if I had a list of 10 invoice #'s, named S1-S10, the vba code created 10 tabs, named S-1, S-2,. S-3.....
Now to my question.
I have a template sheet I want to copy from ( "Template" ), and select any sheet that starts with "S", and copy/paste this template to.
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.
I have an issue new client is asking for me to present my reports with a percent complete. We do survey work and all our date is in Ft. The way my file is set up right now i have H5-H100 as incomplete footage, Column I5-I100 has my completed footage and to keep it simple Row 13 A-k show total amount of footage. He wants to see everyday when i submit the % complete based of footage incomplete and footage completed.
Anyone able to help me out with a formula for this? I tried messing with it a little, wasn't really able to get it down and my Excel skills definitely aren't past beginner level.
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
I have desinged an excel invoice, but I think I might want it to do the impossible.
I have my companies database as a .csv file, is there any function where I can enter in the company account number and it will automatically fill in the company name, address, contact etc like a mail merge type thing and for it to work vice versa enter the company name and it fills in the account number and address all automatically taken from the information on the .csv file?
I want to enter a name in a cell from a list of 70 names I have on another work sheet. By just typing the first few letters of the name into the cell I need it to complete the entry. I do not want to use a drop down box it takes too long to find the name. I need a formula to do this for my spread sheet.
View 6 Replies View RelatedI have a file that i need to use for analysis but it is currently a text file, how can i use VBA to open it with excel and then complete text to columns, using a delimiter of a semi colon.
I have attached a sample of before & After.
I have a form I'm building that I want to put on the server to allow different people to complete as needed.
As they complete this form I want to take all the info they entered and have it go into a data base once there I want to clear the form for the next person.
Is there a way to add auto complete to this Macro so that when a user starts typing in a cell in column A it auto completes.
[Code]....
The idea is that a button on the worksheet 'Sample Info & Results' runs the macro which copies the worksheet to a new file with the name specified in the cell B3 and saves it as a csv format with ";" as the separator. For the most part it is working with the exception that the export stops at row 6 for some reason I dont understand. The code is not mine (credit to T.Thielmans on stackoverflow). code for 1) referencing a folder location within the original file to use as a standard location to save the csv file and/or 2) removing hidden columns.
Code:
Public Sub ExportToCSVFile(FName As String, Sep As String, SelectionOnly As Boolean, AppendData As Boolean)
Dim WholeLine As String
[Code]....
I am attempting to use VBA to open a webpage and then login. Below is the code I have so far:
Code:
Sub Test()
Dim IE As Object
Set IE = New InternetExplorerMedium
With IE
.Visible = True
.navigate ("url")
[Code] ........
Here is the HTML Code of the website:
<form target="_top" name="Logi<wbr>n" method="POST" action="/login.fcc"></form>
<label for="firstname">User Name</label>
<input class="textbox2" onkeypress="javascript:processkey(window.event)" name="USERNAME" type="TEXT">
[Code] ........
An error occurs within the second With, stating that the object variable or With block variable not set.
What would the formula be to complete all three of these functions in one cell?
64
-3
61
When positive & negative then G1+T1=X
-8
-8
0
When negative & negative then G2-T2=X
3
2
1
When positive & positive then G3-T3=X
I have a worksheet named, "Database2". There are hundreds of names in colum B starting in row 3 of this worksheet.
In the same Workbook is a worksheet named, "Records". I want to create a userform with a combo box, an "Enter" and "Cancel" command button. When typing begins in the combo box I need it to recognize the name from the "Database2" list in colum B and executes auto complete. When the "Enter" button is clicked I need it to enter the name in the first available row in colum B starting in row 3 in the Worksheet named "Records".