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

 

[bigbuckaroo]

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

Certainly!! The VBA is part of a SCADA software called RSVIEW32 which is made by Allen Bradley.

Skip,

I will try your suggestion regarding the "MySplit" you suggested and report back.

Thanks

Tom

 

[SkipVought]

On very rare occasions, as seldom as I can arrange, I use Atachmate Extra VBA. It, too, has no Split function.

Skip,

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

 

[bigbuckaroo]

Here is what I now have:

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 = MySplit(s, "(")(0), " ")
nextnum = Format(CInt(lastnum(UBound(lastnum) - 1)) + 1, "0000")

End Function

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 LoadArray
End If
Next
GoSub LoadArray
MySplit = a
Exit Function
LoadArray:
ReDim Preserve a(ai)
a(ai) = Mid(s, i1, i - i1)
il = i + Len(d)
i = i + Len(d)
ai = ai + 1
Return

End Function

However, I am now getting an error in this line of code:

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

The error says "Expected end of statement" and highlights the second , after the (0).

I have tried to modify the line of code but the answer eludes me. Can you take a look and offer some suggestions? What about the rest of the code, does it look ok?

Thanks

Tom

 

[SkipVought]

You fundamentally changed the syntax!

The original code was...

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

Your code is...

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

Can you see the problem?



Skip,

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

 

[bigbuckaroo]

Well really all I see if I go back to your earlier post you said to change Split to MySplit so I changed the code to be:

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

Is this what you are refering to?

When I run the code with it changed to this I now get a "byref argument type mismatch"

I feel like the problem is right in front of me, I just am not able to grab ahold of if.

Thanks for your patience and help

Tom

 

[SkipVought]

ByRef errors mean that you are Referencing the incorrect data type in the data you are passing to the function.

Skip,

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

 

[bigbuckaroo]

I,m sorry. I am just not seeing what the problem is due to my lack of experience with this stuff. I may try again later tonight after I get home.

Tom

 

[SkipVought]

how have you declared the s variable?

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

Skip,

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

 

[bigbuckaroo]

Ok, so with your patience and gentle nudging I pondered your response above and think I am making small baby steps towards understandiing. I did some research and came up with the following modifications in my functions:

Function nextnum(ByVal s As String) As String

Function MySplit(ByVal s As String, d As String)

Is this what you mean by asking how variable s is declared? I am thinking yes.

I step through my code and now I get to this line:

nextnum = Format(CInt(lastnum(UBound(lastnum) - 1)) + 1, "0000")

where now I get a type mismatch error. If I hover my cursor over the expression in different areas, I get different things. For example if I hover over the CInt it shows the whole expression to the )) and says = type mismatch. If I hover over the UBound(lastnum) it says = 16. Hovering over s in the previous line of code gives me this result: s = "2011 06 16 0002(Wide).DBF"

I will keep working at this but if you could again offer some insight that would be awesome.

Thanks,

Tom

 

[SkipVought]

try this instead

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

Skip,

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

 

[bigbuckaroo]

Well, I tried as you suggested but still came up with the same error. When I run with the code you provided I get some different results in the line giving the error than when I did before. Ubound(lastnum) now = 4, lastnum(Ubound(lastnum)-1 = "2011 06 16 0003", str = "2011 06 16 0003"

You did not specifically say so regarding the ByVal that I put in the MySplit function. I tried using that and tried it without it in the code with no difference.

Once again I await your guidance if you desire to provide me with some.

Thanks

Tom

 

[SkipVought]

[tt]
2011 06 16 0002(Wide).DBF
[/tt]
Are you CERTAIN that you have SPACES between your date elements?

When I run that string with the modified code, I get 0017

ALSO, in your original post your structure had a SPACE before the OPEN PARENTHESIS, which will affect your results. With a SPACE there, I get 0003.

Skip,

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

 

[bigbuckaroo]

Due to this being more difficult for me than I anticipated, I have decided to go in a different direction with this whole process. I think to simplify it for me I will create the different subroutines and fire them off at the appropriate time rather then have 1 sub do all the work. I realize for you all with much more experience this probably seems like an inefficient way of doing it. However my frustration has gotten the better of me and I would just like darn thing to work. So, what I have done is create subroutines which are basically the same except for the last four digits of the file name. The last four digits I change from 0000 to 0007 via the VBA code. I use an event file in my "SCADA" software to run the appropriate subroutine at the defined time.

I would like to say thank you to Skip who probably spent considerable time trying to help me figue this out. I can imagine the frustration you felt on your part in trying to help me understand this.

Again thanks,

Tom