Generate A 6 Digits Code Based On Filename And Time Of Generation?
Aug 20, 2009
I need to enter a revision string into a file.
Basically, I need to combine the filename and its generation time and then encode it into a string (i.e. a 6 digits HEX code or 6 digits using [0-9][a-z][A-Z]) so it would generate a pretty much unique revision number.
So, is there any function that you can think of that would do something similar?
If not, what is the best to tackle this?
Generate a CRC or md5sum?
What would be in your opinion a quick and efficient way to solve this tricky one?
View 10 Replies
ADVERTISEMENT
Jan 23, 2014
I have a file that I want to name based on content in certain cells. I.e. "Q-(text from cell G1)-(text from cell B7)-Date(mm_dd_yy)". I want to be able to specify the location where I save this file each time I try to save it, but I want it to update the file's name if any of the parameters have changed.
I have some code generated that does just about everything right except that it automatically saves a version of the file in the folder where the original is located even though it also brings up the dialog box and allows me to save it in another location.
Basically, I want to fix the code below so that it doesn't save what appears to be a backup file each time. Should I be using some sort of savecopyas?
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Add the quote # to the header
ActiveSheet.PageSetup.RightHeader = "Quote No. " & Range("G1").Value
[Code].....
View 6 Replies
View Related
Aug 26, 2008
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
View 4 Replies
View Related
Sep 14, 2012
way for my engineers to save a field ticket with a certain name based on data from a couple of cells in the worksheet. Re: Auto generate "Save As" filename from text and tried to use some code posted in the thread, but I an still not having any luck.
What I want to do is create an active X button when clicked on, would save the workbook to a certain folder. I want the name to look like this:
SO1!M3_SO1!M6_SO1!H2.xls
This is what I have so far:
Private Sub SaveMe()
ThisWorkbook.SaveAs Filename:="C:usersdefaultdesktop" & Range("SO1!M3").Value & Format(Range("SO1!M3").Value, "text") & ".xls"
End Sub
Would I click "general" or "workbook" in VBA when I enter this code?
View 5 Replies
View Related
Jun 3, 2009
Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
View 3 Replies
View Related
May 13, 2007
Below is the code that references a file to import = Jan.txt.
There are different files I want to import, using this macro.
However I have not been successful with creating a variable
and then creating a statement which requires me to input the filename that I want to import for the macro run.
With ActiveSheet.QueryTables.Add(Connection:="TEXT;U:Jan.txt", Destination _
:=Range("A1"))
View 6 Replies
View Related
Nov 9, 2011
I want to have a macro (because i do so often) that for any active workbook that I'm using will....
capture the full pathname and file name and place into clipboard for pasting elsewhere.
eg run macro and the following is added to clipboard.....C:Test DirectoryTest_File.xls
View 7 Replies
View Related
Sep 12, 2012
Need to display time in total minutes but with a space after every 3 digits.
For instance, I can display 0.833333 as total minutes by using the custom number format [m] which displays 1200. But I need it to display as 1 200. I know with a standard number, I could use # ##0 but I don't know how to get both of these custom attributes to work simultaneously.
View 2 Replies
View Related
Apr 28, 2014
I need a VBA macro code to get the output like in the excel file which I have attached with this thread. Which means, I need to group the data for every two hours. in the output I need all 4 cols namely A, B, C, D along with extra col namely, interval based on which I need these groupings to be done..
The code has to be really flexible so that it works for all dates and times in the files. Because like this I have to do for 2000 files.....
Original link: [URL] .....
plant area.xlsx
View 3 Replies
View Related
Jun 25, 2007
I have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.
View 3 Replies
View Related
Sep 23, 2010
Now iam Working in excel 2003, I got a Requirement that, After Downloading datas From SAP, It Directly Stored in Excel Sheet , Using tht Sheet1, I need to rename all the Filenames at a time, Is It possible, Any code is there to rename,
For Example,
From SAP to Excel Sheet Datas are Like this in Sheet1,
DmsNo Filename
50007685 SDFFG.jpg
50004678 HGJKID.jpg
50003421 VGFTHR.jpg
Then i have rename that filename, rename 'SDFFG.jpg' To '50007685.jpg', and it Should be directly rename to my Source File.
For Example:
Source File: C: estSDFFG.jpg
After renaming it Should be,
Source File: C: est50007685.jpg
any code is there to rename,
View 4 Replies
View Related
Oct 31, 2008
I have the following codes in which I am to refer a file and folder to do some further processes.
myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls"
folderPath = "C:Documents and settingsacsMy DocumentsEntry"
I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,
in B5, I would like to enter the Filepath (B5 named as "FILEPATH")
in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")
So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.
View 2 Replies
View Related
Aug 11, 2008
I have a spreadsheet that from a button I want to run a macro that will input todays date, the value in cell A1 as the filename into a default dialog box that is at a default file path. I have been trying to do this for several hours and can not completely get it done.
View 9 Replies
View Related
Apr 24, 2008
each time a button is clicked I want a new ref number to be produced and displayed on my userform.
I can't however create the code to generate this ref number.
In column A of my 'sweeplog' worksheet is where I want the ref number to be placed. This ref number is simply made up of today's date - in format ddmmyy eg 240408 for today 24th April 2008 then add the number '1' if its a new date or the next number in coulumn A if not.
for example In column A I have these 3 ref numbers
A2 = 2404081 (24th April 2008 plus 1 as this is the first ref used today)
A3 = 2404082 (as above but with 2 added)
A4 = 2404083 (as above but with 3 added)
now tomorrow the next ref number that would be shown in column A (after the user clicks the button) would be as follows.....
A5 = 2505081 (25th April 2008 plus 1 as this is first ref used on 25th)
I am able to get this unique ref number to show in my userform but can't figure out how to produce the code to generate it
View 9 Replies
View Related
Oct 31, 2008
i need to format my numbers in the following format
10,00,000.00
the first three digits will be separated and then subsequently 2 digits
View 2 Replies
View Related
Mar 19, 2013
modifying the following VBA code; this code auto-generates 4-digit unique numbers, using zero as one of the starting digit as well. I need the 4-digit numbers NOT to begin with a zero, the 4-digit numbers should only begin with numbers 1-9.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Test As Integer
[Code]....
View 2 Replies
View Related
Jan 13, 2008
how to sort based on the first two digit..
I need to sort the COMM CODE ( which is at Column no 3 )
View 14 Replies
View Related
May 27, 2008
I would like to extract numbers based on the first 2 digits (22....) of a column along with its next column (on the right) and put them on another sheet.
View 9 Replies
View Related
Jan 22, 2014
I want to develop a macro for getting filename without extension and seperating the value into two variables.
Requirement:
I have a folder with 3 subfolders inside.
Each subfolder has few files in it.
I will give main folder as input and it should check all subfolders/main folder for files and take that files and modify as shown in below example:
Example of one file: TEST-123_REV00.pdf
Here TEST-123 is the document name
REV00 is the revision of the document
pdf is as usual the extension.
Now my requirement is to prepare an output file with all these files in a text file.
The output is in this format..
Document Name | Revision Name.
View 4 Replies
View Related
Nov 29, 2006
I have code that takes full cell with filename, looking for it in certain directory and copying to another directory.
Sheets("Unique").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection
If rng.Offset(0, 4).Value <> "" And InStr(1, rng.Offset(0, 4).Value, "http") = 0 Then
FileCopy SourcePath & rng.Offset(0, 4).Value, ThisWorkbook.Path & "" & DestPath & "" & rng.Offset(0, 4).Value
End If
Next rng
I want to change the code that it will copy based on number. Any suggestions?
Example:
I have range of filenames.
10989478.MEC0388A-L0_RoHS_globemotors_1.pdf
10989495.Keystone_M1.4cat. zip
10989559.MEC0388A-L0_RoHS_qualtek.pdf
I want to copy files starting with 10989478, 10989495, 10989559. I mean ignore the part after "dot".
View 9 Replies
View Related
Apr 30, 2014
I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.
=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)
View 12 Replies
View Related
May 26, 2006
I have 4 and 5 digit numbers. For the 4 digit numbers, I want to be able to distinguish between the numbers by the last digit. For the 5 digit numbers, I want to be able to distinguish between the numbers by the last two digits.
Example: For 4 digit numbers, I would like to do something with all numbers ending in 1, 2, 3, 4, 5, 6, 7, and 8. For 5 digit numbers, I would like to do something with numbers ending in 10, 11, 12, 20, 21, etc.
The first step in being able to do this, I guess is distiguishing between 4 and 5 digit numbers, which I know can be done by the number lenght. However, the second part of looking at the last digit or the two last digits I don't know how to do.
View 4 Replies
View Related
Jul 5, 2007
I am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!! The formula I am trying to do is something like this...
In column S:S
IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1"
IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2"
IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3"
IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"
Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.
View 7 Replies
View Related
Jan 10, 2013
If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".
I can generate the consecutive numbers, but have not figured out how to generate the required string.
View 2 Replies
View Related
Jul 31, 2014
I'm currently creating E-mails containing a table of material numbers in the body of the message. The E-mail is generated with a macro from a table in excel. In some folder - say on the desktop - I have PDF files for each material Number. Each file is called 0012345_Product-Name.pdf where 0012345 is the material number (always 7 digits). I want the macro to look for the pdf for each material number found in the table and add it to the E-mail as an attachment. With constant, known file names this wouldn't be much of an issue. However, I'm having trouble getting the macro to only search for the first 7 digits of the filename and select it based on that.
Since we are talking up to several 100 PDF's, it would also be great to zip them, once selected - if at all possible, before attaching them to the E-mail
View 2 Replies
View Related
Feb 3, 2009
Is it possible to create a macro attached to a button that allows me to save the workbook based on content of 2 cells in the workbook?
I like to save the workbook based on content of cell A4 that contend a text string. After that saving another time with the contend of cell A4 plus cell A9 which contend the today() function. I am doing this because this workbook is constantly updated and a backup copy is done based on the date the file is save.
View 9 Replies
View Related
Dec 13, 2013
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
1002856101
22781721
1044863815
1008799064
1044779765
1006511115
1007641804
0729939256
5303486020
8364709
0014094759
0019921519
8172717
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------
=IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's.
-----------------------------
View 3 Replies
View Related
Mar 17, 2006
I have a very large database where I want to filter out numbers that do
not have certain two digits as their last two. For example, the column
based on which I want to filter out data contains numbers like
197301310153. I want to filter out anything that does not end in 53....
View 13 Replies
View Related
Jul 16, 2009
I am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)
View 2 Replies
View Related
May 24, 2007
If a user inputs "Dispatch" into any cell on column G, I need to generate a Msgbox pop up.
View 3 Replies
View Related
