Creating New Suffix For A File Each Time Code Run
Jun 17, 2014
I have a piece of code that runs and creates an output Excel sheet that currently has the run date as a suffix for the automatically generated file name. However, I now sometimes have to run this report multiple times in one day, which currently means overwriting the previously run spreadsheet (whereas I would like to keep both versions of the ruin output).
Is there a way that I can create some kind of counter for the number of times the report is run and add that as a suffix for the file name? At the moment the only way I can think to do this is to set up code that looks at the file name containing the date field and adds 1 to every number that is added as a suffix, but this doesn't seem very efficient...
View 2 Replies
ADVERTISEMENT
Mar 2, 2007
I need to distribute it to several areas of my company. Each area only needs part of the report. Therefore I have written some code which splits the existing report by area and creates a brand new report with that smaller chunk of data in it.
However I now realize that the new sub-report will require some code of it's own in order to function. My problem is that only the sub-report will be distributed to the end user. The core report will not. Therefore any code written for the new sub-report must be included within the sub-report.
View 4 Replies
View Related
Jul 30, 2014
From an Excel 2003 workbook I generate some product labels. Each label has a 5 digit job number and a quantity that are passed to variables. If the quantity is 1, then I have no problems in printing my label. My problem is where the quantity is greater than 1. If a job has more than 1 item (can be up to 40 or 50) I need to add a series of letter (or letters) to the end of the job number.
For example:
Job number 12345
qty 1
number on label 12345
Job number 12346
qty 4
numbers on labels 12346A, 12346B, 12346C, 1234D
so that each printed label has a unique job number
View 3 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
Feb 8, 2007
I have a time card that is split into sections so there are a lot of SUM formulas. However, I need to include a space for breaks - 10 min.
How do I make everything convert comfortably?
Ex. 5 hours allotted to person A. 5 hours to person B. 1 hour 40 allotted to tasks. And 20 min per day for breaks.
Is there a way to format the cell so that it displays hours and minutes
1:35 = 1hr35min ??
All the formats available show it as time. I need it as a quantity of time rather than time of day so that the formulas will add correctly.
View 9 Replies
View Related
Feb 8, 2007
I want to generate a text column for a mail merge which will add the correct ordinal suffix to dates eg st to 1 as in 1st or 21st or 31st but nd to 2nd 22nd also rd and th where appropriate.
nested IFs?
View 9 Replies
View Related
Sep 11, 2007
I have a macro that will add the next letter to a number as a suffix but this will only work for A - Z. But i need variations of upto 150, so i need the macro to add another letter to the variation once Z has been reached. For example at the moment 1000A the next variation would be 1000B up until 1000Z but once this has been reached the macro then starts to use symbols as the code is written using ASCII numbers. So i need the next variation to be 1000AA then 1000AB etc the code i currently have is
Public Function NewVarianceLetter(lngQuoteNum As Long, Optional rngQuote As Range) As String
Dim a As String
Dim i As Long
Dim rngFind As Range
View 9 Replies
View Related
Mar 1, 2007
We have two characters : One debit shown as Dr
Another Credit shown as Cr
we have an excel sheet containing a problem as under:
row 1 row2
column 1 Sales 345670 Dr
column 2 Receipt 340000 Cr
column 3 TDS 5670 Cr
We have replaced the Suffix so that it looks as under:
Sales 345670
Receipt 340000 Cr
TDS 5670 Cr
Wewant the last two lines to have figures in the negative ie - 340000
- 5670
i.e. cr suffix change to negative figure -340000
View 9 Replies
View Related
Jul 23, 2006
I work with product codes containing suffices and want to copy the product codes to a different column without the suffix.
Example: 960-1043 25KG to be copied to a cell in a different column as 960-1043 only
View 3 Replies
View Related
Aug 1, 2013
Each day I recieve a spreadsheet with thousands of row. In col A there is data that has "-NCA1" or "NCA2" as the suffix. I have to "delete" these rows before proceding.
I'd like to know if there is a way to do this with a macro and ideally delete a wildcard suffix with "NCA*" in case any 3...4..5 etc are added.
View 4 Replies
View Related
Dec 4, 2013
I am trying to create a formula that subtracts 1 hour if the value of cell E3 equals 11:00 PM.
This is what I've tried:
=SUM(E3-D3), IF(D3=11:00 PM, -1:00)
View 9 Replies
View Related
Feb 13, 2009
Hi i have alot of values that i would like to display in a graph over time.
The problem is, is that there are many different times (irregular intervals) that the value is recorded during each day.
whenever i create a graph and choose the x-axis format as the date i get a spike at each day, which is no good!
value Date Date Time
0 22/12/2008 12:33:35 22/12/2008 12:33:35
0 22/12/2008 20:53:25 20:53:25
9 23/12/2008 00:48:48 23/12/2008 00:48:48 ....
View 9 Replies
View Related
Aug 4, 2006
I need the users to be able to enter the time using either a . or a colon :,
So: 13:30 would give 13:30 and 13.30 would give 13:30. They have end up in time format too since I will be performing calculations on them. When that decimal place is entered I would like it to be turned into a dot
View 3 Replies
View Related
Jan 14, 2007
code that creates hyperlinks by the column or row. I Have a spreadsheet with some code which archives data into a summary sheet and creates a backup sheet of the detail. This all works really well. In the summary sheet, I have a column that is named with the name of the new worksheet.
What I want to do is to be able to create a hyperlink from this name in the column to the new, backed up worksheet. I do not want to create a list of hyper links, I only want to create on link at a time, each time I run the code.
View 3 Replies
View Related
Aug 7, 2012
Is there a way to separate the suffix from various addresses i.e
A1= FOSTER AVE
A2=WISE
A3=EL DORADO ST
A4=OLD HIGHWAY 221
Some of the addresses are up to 5 words and numbers, some are missing the suffix all together.
View 6 Replies
View Related
Aug 29, 2012
I find, when performing a 'save as', xl puts in a default filename with a suffix (1), or (2), or (3) and so on, to the existing filename, after I have opened a file from MS Outlook. I had assumed it was because there was already an existing file in the default directory, and so it was creating another version, but no other files with the same name exist in the default directory being saved to, or on the remainder of my computer, so I don't understand why this occurs.
View 2 Replies
View Related
Nov 27, 2006
I am using (in Column "B") the formula: =IF(A2=A3."",1), copied down as required, to uniquely identify entries in Column "A". In this instance "10", "10A", "10B" and "10C" located in "A5" through "A11" are viewed as the same. Is it possible to have the formula recognize the enties in "A5:A11" as a unique entry and therfore have "1" entered into cell "B11"? The suffix, if used will always be A thorugh Z.
View 9 Replies
View Related
Mar 16, 2007
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
View 9 Replies
View Related
Mar 2, 2010
I've got numerous vba-coded spreadsheets in use with lots of different users, and have found that some users try to hide errors of their own making by blaming the spreadsheets (I know - what is the world coming to!)
I've decided to add a script which creates a log file each time one of my spreadsheets is used - opening a log which relates to the user's spreadsheet and recording username, date/time, path & file, and the vba being used.
I'll probably add an "on error" script to save copies of all open workbooks too.
The issue I have at the moment is how to record the name of the macro being run?
Here's my code so far: ....
View 9 Replies
View Related
Oct 2, 2007
Create a cell with running time in Hours, Minutes, and seconds, in 12:00:00 AM format.
View 13 Replies
View Related
Jul 29, 2008
I would like to add something to my workbook which, when called, gives you current time in Tokyo, Australia, USA, London, Europe (for example).
I am trying to work out what would be best.
Would it be possible to create this on the Status bar?
Alternatively, i guess a Userform shown as modeless (but i am not sure if the controls holding the times will update.
View 9 Replies
View Related
Jan 9, 2014
Is there a formula that will allow you to add an ascending suffix to duplicate data in a column?
For example:
1234
1234
1234
1234
1234[code].....
I've been trying to figure it out on my own for a while now with no luck...
View 7 Replies
View Related
Oct 3, 2008
I have a column filled with 5 digit numbers and a suffix. (Ex: 12345-001) I need to delete all of the '-001' from all of the numbers in the column. There are over 11,000 numbers. Is there a formula or function that I can apply to the entire column that will delete the suffix?
View 2 Replies
View Related
May 5, 2009
I have a text document to which i need to do some changes and then turn it into CSV. Here are some lines of what the document looks like and below i will show you what i need it to look like. So i open the document with Excel and next I'm not sure how to do what i will show you below. Original:
HTML 111text
112text
113text
Here is how i need it:
HTML 1,1,1,"text"
1,1,2,"text"
1,1,3,"text"
View 5 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 29, 2009
I have some code that looks at the part # in column D and then goes out to the D drive to drawings (.pdf files) and creates a hyperlink.
The problem is that it is only creating a link to the .pdf file and not to folders. I need a hyperlink to the folder so when it is clicked the open folder pops up and shows the multiple files associated with that part.
Link should be the same regardless of file or folder, no? As long as the name matches link should be made, correct?
E.g.
123-100.pdf (column D part #) links to 123-100.pdf file on CD
456-200 (folder name in column D) no link created to folder named 456-200 on CD.
Sub Hyper()
MyPath = "D:"
StartRow = 2
EndRow = 20
x = 0
For i = StartRow To EndRow
Cells(i, 4).Select
MyFileName = ""
MyFileName = Dir(MyPath & Cells(i, 4).Text, vbNormal)
If MyFileName "" Then
x = x + 1
Cells(i, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:" & MyFileName
End If
Next i
End Sub
View 9 Replies
View Related
Oct 6, 2006
each row in sheet1 contains variables that, when applied to cells in sheet2, calculate a final figure. I have written code that selects each row, distributes its variables in sheet2, and shows the result back in sheet1, then selects the next row, and so on until all rows have been calculated.
The calculation is complex, and I would like to create a text file that stores the calculation for each row in the same text file. The calculation is a 9 columns by 33 rows, and I would like to preserve this shape in the text file, though its OK not to have columns not lining up.
There is a similar problem on an old thread but this requires only one iteration of "open file, write values, close file". I need "open file, write values, write more value, close file".
View 4 Replies
View Related
Apr 2, 2007
I have many users of the same spreadsheet on different PCs writing .txt files to a common directory on a central server. I want to be able to create a uniquely named .txt file, using a set naming convention (datetimeotherthingsetc.txt) each time they execute my export code routine. Is there a way I can create a txt file from vba code? I have figured out how to write into an existing .txt file but would like to know if I can create a .txt file from scratch from vba code? ps to avoid any confusion I dont want to save (ie save as) the open spreadsheet to a .txt file
View 4 Replies
View Related
Aug 6, 2014
I seem to struggle with excel formulas when time is involved. I am trying to create a spreadsheet that reviews outcome measures (walking tests in this case) to outline firstly if someone has improved or not (Outcome in the blue columns) which seems to be working alright. This takes into account the 2 aspects of time taken to complete the test and the walking aid required.
What I would now like to be able to do is compare each individuals 'After' score with a normal range for that test. I have put a list below the table of the ranges I am looking for. I have tried to put time formats into a long IF formula to try and get the ranges covered for the TUAG test, but doesn't seem to be working.
The 10 metre walk test is a little trickier as this is looking at change in velocity between the before and after scores. I think I need to be able to work out the velocity for each test and then do a comparison, but again bit stuck with the time formatting.
View 2 Replies
View Related
Jun 11, 2008
I am creating 10 pivot tables for data in one worksheet and putting them in a separate work sheet. i used the macro recorder but the cod eit worte stop at this line ...
View 9 Replies
View Related
