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

VBA Code To Generate Filename Without Actually Saving File

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

Code To Generate Data Table Based On Variables

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

Auto Generate Save As Filename From Multiple Cell Data?

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

Auto Generation Of String Based On User Input

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

Vba - Input Filename To Open Each Time

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

VB Code To Capture Pathname And Filename

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

Display Time In Total Minutes But With A Space After Every 3 Digits

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

Vba Macro Code For Grouping Data Based On 2 Hours Time

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

Vba Code Which Saves A Workbook With A Variable As The Filename

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

Excel 2003 :: Renaming All FileName In Folder Using Macro At Time With Different Names

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

Refer Folder/Filename In VBA Code Through Named Cell Reference

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

VbA Code To Automatically Put Date Filename And Default Save Location

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

Code To Generate 'date' Ref Number

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

Number Formatting: The First Three Digits Will Be Separated And Then Subsequently 2 Digits

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

Modifying VBA Code To Generate 4-digit Numbers?

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

Sorting In WorkSheet Based On First Two Digits

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

Extract Numbers Based On First 2 Digits

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

Getting Filename Without Extension And Separating Value Based On Character?

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

Copy Files Based On Part Filename

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

VLookup Only Right Most 4 Digits Of The 6 Digits Sequential Numbers

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

If Statement Based On Last Digit Or Last Two Digits In A Number

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

Join Text Based On Value Of Left Digits

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

Using VBA Code To Generate List Of Consecutive Numbers And Convert Into String

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

Attach Files To Email Based On Partial Filename?

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

Macro For Saving Filename Based On Cell Content

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

Formula For Assigning Names Based On Term Digits

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

Data Filtering Based On Last Two Digits Of Large Number

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

Filter Sheet Based On Certain Digits In A Column Of Part Numbers

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

Generate Pop-up Based On Specific Value

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







Copyrights 2005-15 www.BigResource.com, All rights reserved