file conversion

[Rpettis31]

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

 

[Gary''s Student]

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

 

[Mark Ivey]

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]

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]

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