Change file name programmatically

[bigbuckaroo]

Greetings all experts. I am having difficulty figuring out how to do this. I have an application that creates a file at certain times throughout the day. I am then using VBA to send the file to a list of recipients.

Here is the code I have so far:

Sub QC_TEMP_CHECKS_log_send()
Set App = CreateObject("Outlook.Application")

Set NameSpace = App.GetNamespace("MAPI")

NameSpace.Logon

Dim SafeItem, oItem
Set SafeItem = CreateObject("Redemption.SafeMailItem") 'Create an instance of Redemption.SafeMailItem
Set oItem = App.CreateItem(0) 'Create a new message
SafeItem.Item = oItem 'set Item property
SafeItem.Recipients.add "xxx@yyyy.com"

SafeItem.Recipients.ResolveAll
SafeItem.Subject = "QC TEMPERATURE CHECK"
Dim oFS As Object

 
Set oFS = CreateObject("Scripting.FileSystemObject")

Dim sfle As String, sfol As String

sfol = "D:\QC_TEMP_CHECKS_LOG\QC TEMP CHECKS\" ' change to match the source folder path

sfle = Format(Date, "yyyy mm dd") & " 0000 (Wide).DBF" ' change to match the file name

Set attach = SafeItem.Attachments.add(sfol & sfle)

The problem I am having is the file name gets changed where the "0000" is so that on each creation this number increments by one. So then the next file name is would be yyyy mm dd 0001 (Wide).DBF.

I'm sure there has to be an easy way to do this but it is not coming to me.

Anyone care to give me a nudge in the right direction?

Thanks,

Tom

 

[SkipVought]

hi,

float this...

sfle = Format(Now, "yyyy mm dd hhmm")

Why are you using SPACES in a file name?

Here's an example of a sturcture that I use...
NEW_List1_2011-06-07_2103

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bigbuckaroo]

Skip,

First off thanks for responding. The file name is generated by a software application. The spaces are part of the file name and I can not do anything with them as far as I know.

Not sure if your suggestion is what I am looking for. The 0000 in my file name does not reference the hour and minute. It is incremented by 1 when a new file gets created on the same day.

For example the software application is checking temperature in a series of vessels and creating a file to record the values. I then trigger the above VBA code to send the file to the QA department. So starting at 3:00am the file name would be 2011 06 08 0000 (Wide). At 6:00am the file name created would be 2011 06 08 0001 (Wide), and so forth.

Again thanks for the assistance.

Tom

 

[SkipVought]

the argument value would be your file name...

Function NextNUM(s As String) As String
    Dim LastNUM
    LastNUM = Split(Split(s, "(")(0), " ")
    NextNUM = Format(CInt(LastNUM(UBound(LastNUM) - 1)) + 1, "0000")
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Why not simply use the Dir function to retrieve all the files of the day ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bigbuckaroo]

Wow! That might take me some time to digest since I'm sure you can tell I am quite the novice when it comes to VBA.

I will do some experimentation and see what I can come up with.

Thank you

Tom

 

[bigbuckaroo]

Why not simply use the Dir function to retrieve all the files of the day ?"

Answer:
The goal is to record the temperatures at specific times. I agree it would be much simpler to send one file with all the checks for the day. This is not what is desired by the QA department. Currently a lab tech manually records the readings. What they do with the information I know not. I suggested sending them the data electronically. They seemed to like that idea, so they still desire it every 3 hours

Thanks

Tom

 

[SkipVought]

I missed a step or two...

Dim oFSO, oFile, sPrevFile As String

Set oFSO = CreateObject("Scripting.FileSystemObject")

For Each oFile In oFSO.getfolder("D:\QC_TEMP_CHECKS_LOG\QC TEMP CHECKS\").Files
    If sPrevFile < oFile.Name Then sPrevFile = oFile.Name
Next

sfle = Format(Date, "yyyy mm dd ") & NextNUM(sPrevFile) & " (Wide).DBF"   ' change to match the file name

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bigbuckaroo]

Ok now with the added steps it becomes a little more clear. I will give it a try and get back with the results.

I sincerely am greatful for your help.

Tom

 

[bigbuckaroo]

Ok so after a couple of days I am getting back to this again. I spent some time and this is the code I have so far:

Function nextnum(s As String) As String
Dim lastnum
lastnum = Split(Split(s, "(")(0), "")
nextnum = Format(CInt(lastnum(UBound(lastnum) - 1)) + 1, "0000")

Dim ofso, ofile, sPrevFile As String
Set ofso = CreateObject("scripting.filesystemobject")
For Each ofile In ofso.GetFolder("D:\QC_TEMP_CHECKS_LOG\QC TEMP CHECKS\").Files
If sPrevFile < ofile.Name Then sPrevFile = ofile.Name


Next



End Function


Sub QC_TEMPS_LOG()



Set App = CreateObject("Outlook.Application")

Set NameSpace = App.GetNamespace("MAPI")

NameSpace.Logon
Dim SafeItem, oItem
Set SafeItem = CreateObject("Redemption.SafeMailItem") 'Create an instance of Redemption.SafeMailItem
Set oItem = App.CreateItem(0) 'Create a new message
SafeItem.Item = oItem 'set Item property
SafeItem.Recipients.add "xxxxxxxxx"

SafeItem.Recipients.ResolveAll
SafeItem.Subject = "TEMPERATURE LOG"
Dim oFS As Object
 
Set oFS = CreateObject("Scripting.FileSystemObject")

Dim sfle As String, sfol As String, sPrevFile As String

sfol = "D:\QC_TEMP_CHECKS_LOG\QC TEMP CHECKS\" ' change to match the source folder path

sfle = Format(Date, "yyyy mm dd ") & nextnum(sPrevFile) & " (Wide).DBF ' change to match the file name"

Set attach = SafeItem.Attachments.add(sfol & sfle)

SafeItem.Send


End Sub

When I step through my subroutine I get to this line:

sfle = Format(Date, "yyyy mm dd ") & nextnum(sPrevFile) & " (Wide).DBF ' change to match the file name"

and then it goes to the function routine and gives me a compile error: sub or function not defined and it highlights the second Split in this line of code:

lastnum = Split(Split(s, "(")(0), "")

Can someone please take a look to see what is wrong with this and offer some advice?

Thanks

Tom

 

[SkipVought]

At that point when you DEBUG, what is the value of s

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bigbuckaroo]

I hope I am doing this correctly. When I highlight s, click on DEBUG,then click on ADD WATCH the following comes up at the bottom of the screen:


Watch : : s : <Can't compile module> : Empty : ThisProject.nextnum

When I go to DEBUG then COMPILE I get an UNEXPECTED ERROR 32811 but when I go through my code I do not have anything that is highlighted as an error.

Any ideas?

Thanks

Tom

 

[SkipVought]

where do you have nextnum stored?

Please post your nextnum code exactly as you have it in your project.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bigbuckaroo]

Here is a copy of the code:

Function nextnum(s As String) As String
Dim lastnum
lastnum = Split(Split(s, "(")(0), " ")
nextnum = Format(CInt(lastnum(UBound(lastnum) - 1)) + 1, "0000")
Dim ofso, ofile, sPrevFile As String
Set ofso = CreateObject("scripting.filesystemobject")
For Each ofile In ofso.GetFolder("D:\QC_TEMP_CHECKS_LOG\QC TEMP CHECKS\").Files


If sPrevFile < ofile.Name Then sPrevFile = ofile.Name


Next



End Function

As for the location for where it is posted, I will try to give some backround information as well.

The software that I am using is a SCADA package from Allen Bradley called RSVIEW32. In this software I have opened the VBA editor. The window that opens is where all the VBA code has been written to. The code consists of about 38 subs dealing with temperature and cooling checks of our vessels.

I hope this helps you to help me

Thanks,
Tom

 

[SkipVought]

nextnum needs to be called in you main procedure, like...

Sub Main()
    Dim ofso, ofile, sPrevFile As String
    Set ofso = CreateObject("scripting.filesystemobject")
    For Each ofile In ofso.GetFolder("D:\QC_TEMP_CHECKS_LOG\QC TEMP CHECKS\").Files
    
    
        If sPrevFile < ofile.Name Then sPrevFile = ofile.Name
    
    
    Next
    
    MsgBox nextnum(sPrevFile)
    
End Sub

Function nextnum(s As String) As String
    Dim lastnum
    lastnum = Split(Split(s, "(")(0), " ")
    nextnum = Format(CInt(lastnum(UBound(lastnum) - 1)) + 1, "0000")
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bigbuckaroo]

I now have the following code:

Sub QC_LOG_FIND()
Dim ofso, ofile, sPrevFile As String
Set ofso = CreateObject("scripting.filesystemobject")
For Each ofile In ofso.GetFolder("D:\QC_TEMP_CHECKS_LOG\QC TEMP CHECKS\").Files


If sPrevFile < ofile.Name Then sPrevFile = ofile.Name


Next

MsgBox nextnum(sPrevFile)

End Sub



Function nextnum(s As String) As String
Dim lastnum
lastnum = Split(Split(s, "(")(0), " ")
nextnum = Format(CInt(lastnum(UBound(lastnum) - 1)) + 1, "0000")

End Function

When I run the sub it still goes to this line in the function:

lastnum = Split(Split(s, "(")(0), " ")

then it generates the error saying sub or function not defined and highlights the second instance of split.

I apologize for my lack of understanding of this and sincerely am thankful for all the help you have provided.

Is there still something I am doing incorrectly with this?

 

[SkipVought]

Your version of VBA, if you are not programming in Excel VBA, may not have the split function.

Paste this into your module and then change Split to MySplit when you call...

Function MySplit(s As String, d As String)
    Dim b As String, i As Integer, a(), ai As Integer, i1 As Integer
    
    i1 = 1
    For i = 1 To Len(s)
        b = Mid(s, i, Len(d))
        If b = d Then
            GoSub LoadArry
        End If
    Next
    GoSub LoadArry
    MySplit = a
    Exit Function
LoadArry:
    ReDim Preserve a(ai)
    a(ai) = Mid(s, i1, i - i1)
    i1 = i + Len(d)
    i = i + Len(d)
    ai = ai + 1
    Return
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bigbuckaroo]

Excel was recently added to the computer that runs this application. I have it added to the references so I would assume it could be used in my VBA code right? This would be preferable as I would also at a later time like to use Excel to modify the created file. That part is for a later time though. I am thinking I have to open an instance of Excel in my VBA code. Is this correct?

Thanks,

Tom

 

[SkipVought]

The Excel Object Library is not the VBA library. If you do not reference Excel in this code, you do not need the Excel object library.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

Can we just find out what application you are currently using to host your VBA?