Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
file conversion
I receive files 2x daily with tracking information that is in a txt format.
The files come in the name format example GEN_UNIEK_CNTRMAN_2008_15_03_34. I would like to know how to get the time date stamp to access these files by using the GEN_UNIEK_CNTRMAN_2008-* and the date to open these files. I have code that works to convert the file and save it as an xls. So I just need to know how to pull these other files that have been stored for the past year. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
file conversion
In this example, I am using the variable s4 to hold the _34 as I don't know
how to generate it automatically. The rest of the code shows how to build a string in the correct format from today's date: Sub pettis() Dim s1 As String Dim s2 As String Dim s3 As String Dim s4 As String s4 = "_34" s1 = "GEN_UNIEK_CNTRMAN_2008" mnth = Month(Now) If mnth 10 Then s2 = "_" & mnth Else s2 = "_" & "0" & mnth End If dy = Day(Now) If dy 10 Then s3 = "_" & dy Else s3 = "_" & "0" & dy End If MsgBox (s1 & s3 & s2 & s4) End Sub -- Gary''s Student - gsnu200773 "Rpettis31" wrote: I receive files 2x daily with tracking information that is in a txt format. The files come in the name format example GEN_UNIEK_CNTRMAN_2008_15_03_34. I would like to know how to get the time date stamp to access these files by using the GEN_UNIEK_CNTRMAN_2008-* and the date to open these files. I have code that works to convert the file and save it as an xls. So I just need to know how to pull these other files that have been stored for the past year. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
file conversion
Do you want to access the files' properties, or just to work with the file
names? If you want to work with the file names, please explain what the original format is (GEN_UNIEK_CNTRMAN_2008_15_03_34). Mark "Rpettis31" wrote in message ... I receive files 2x daily with tracking information that is in a txt format. The files come in the name format example GEN_UNIEK_CNTRMAN_2008_15_03_34. I would like to know how to get the time date stamp to access these files by using the GEN_UNIEK_CNTRMAN_2008-* and the date to open these files. I have code that works to convert the file and save it as an xls. So I just need to know how to pull these other files that have been stored for the past year. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
file conversion
The file name is then GEN_UNIEK_CNTRMAN_DATE _random number
Therefore I was hoping to use the files properties to open all the files with the standard name with a * as a wild card. So basically is the file name GEN_UNIEK_CNTRMAN_2008-* if so is this file on this date. I was thinking to run a loop date-= (date=data-1)to go to the next date to open the next file. The way the program runs it does just the current date and I have a back log of files in the folder. "Mark Ivey" wrote: Do you want to access the files' properties, or just to work with the file names? If you want to work with the file names, please explain what the original format is (GEN_UNIEK_CNTRMAN_2008_15_03_34). Mark "Rpettis31" wrote in message ... I receive files 2x daily with tracking information that is in a txt format. The files come in the name format example GEN_UNIEK_CNTRMAN_2008_15_03_34. I would like to know how to get the time date stamp to access these files by using the GEN_UNIEK_CNTRMAN_2008-* and the date to open these files. I have code that works to convert the file and save it as an xls. So I just need to know how to pull these other files that have been stored for the past year. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
file conversion
See if you can get started with something like this...
Mark Sub test() Dim fs As Variant Dim fn As Variant Dim dtc As Variant Dim strPath As String Dim strFile As String strPath = "C:" ' set directory as needed strPath = strPath & "\" ' add backslash to path ' strFile will contain the path and the starting ' filename you were wanting to target strFile = Dir(strPath & "GEN_UNIEK_CNTRMAN_2008*") Do While strFile < "" Set fs = CreateObject("Scripting.FileSystemObject") Set fn = fs.GetFile(strPath & strFile) ' you can play around with the format used in the ' dtc value. this value will represent the date ' and time of creation for each file you have in ' the assigned directory and for the given starting ' filename. ' I personally would feed these values into some ' type of an array and then run a sort on it to ' get it in some type of logical order. but that ' is totally up to you. the format I used is very ' similar to that used with database administrative ' analysis. this format will allow for an easy ' mathematical sort. ' the format used here is (yyyymmddhhmmss): ' yyyy = year ' mm = month ' dd = date ' hh = hour ' mm = minute ' ss = second dtc = Format(fn.DateCreated, "yyyymmddhhmmss") ' your code would go in here strFile = Dir Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Conversion | Excel Discussion (Misc queries) | |||
File conversion | New Users to Excel | |||
AppleWorks file conversion to Excel file | Excel Discussion (Misc queries) | |||
old xls file conversion | Setting up and Configuration of Excel | |||
.csv file conversion | Excel Programming |