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?
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
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?
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?
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.
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"))
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.
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.....
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.
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,
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.
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.
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
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
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.
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".
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)
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.
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.
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
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.
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:
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. -----------------------------
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....
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)
