File Management Issues

[Tills13]

Hey everybody, I have a quick question regarding file operations and moving/renaming them in particular.

Here's my code:

FileAddress = "X:\Database\MTR's\PDF Folder\" + EditMTR.HeatNumber
If PONumber.Value <> "" Then FileAddress = FileAddress + "-" + PONumber.Value

Set FSO = CreateObject("Scripting.FileSystemObject")
Count = 0
Do While FSO.FileExists(FileAddress) = True
Count = Count + 1
FileAddress = FileAddress + "(" & Count & ")"
Loop

Name Files As FileAddress
Files = FileAddress & ".pdf"

If ActiveSheet.Name = "MTRs" Then Location = ActiveCell.Row Else: Location = Sheets("Search").Cells(ActiveCell.Row, 20).Value
Sheets("MTRs").Hyperlinks.Add Anchor:=Cells(Location, 1), Address:=Files
If ActiveSheet.Name = "Search" Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(ActiveCell.Row, 1), Address:=Files

It kind of works in that it renames it, checks to see if the file exists, if it does, increments it, if it doesn't, moves it, and links it properly in the sheet. The issue is that it seemingly competely ignores that whole "Files = FileAddress & ".pdf"" line. The files in the destination folder are of no type and cannot be opened without manually adjusting the file extension - which is what I thought I was doing in the code.

What am I doing wrong?

 

[kpark91]

Try debugging it.
I think the problem lies in your string manipulation which I can't see on this side.

 

[Tills13]

Yikes, I got it now.

Is there any way to make this code more efficient? It seems to hang on the "Name" line.

FileAddress = "X:\Database\MTR's\PDF Folder\" + EditMTR.HeatNumber
If PONumber.Value <> "" Then FileAddress = FileAddress + "-" + PONumber.Value

Set FSO = CreateObject("Scripting.FileSystemObject")
Count = 0
Do While FSO.FileExists(FileAddress) = True
Count = Count + 1
FileAddress = FileAddress + "(" & Count & ")"
Loop

[B]FileAddress = FileAddress & ".pdf"[/B]
Name Files As FileAddress
Files = FileAddress

If ActiveSheet.Name = "MTRs" Then Location = ActiveCell.Row Else: Location = Sheets("Search").Cells(ActiveCell.Row, 20).Value
Sheets("MTRs").Hyperlinks.Add Anchor:=Cells(Location, 1), Address:=Files
If ActiveSheet.Name = "Search" Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(ActiveCell.Row, 1), Address:=Files

It was quite a simple fix and I feel silly for missing it. :P

 

[kpark91]

It happens to any of us :P

I'm not too sure what Name _ As _ does.. but it just seems like you want to set Files = FileAddress
Why doesn't you just dim Files as String and set it as fileaddress?

 

[Tills13]

Name _ as _ basically renames a file and then, if the path is different, moves it. It seems to take forever only on certain files, but I'll see if I can't figure out what's going on through the debugger.

As for Files. Files is a Global variable that is used in other functions, so I can't just set it to FileAddress before I enter the code segment I've posted.

 

[kpark91]

Tills instead of using Name function which seems to perform other operations when you only need to do one thing, why don't you create a function which does it but with FSO (File System Object)?

 

[Tills13]

I use a FSO to attach a file to Files - it's just not in the code I supplied.

Files = Application.GetOpenFilename(, , "Attach PDF", , False)
If Files <> Empty Then
Preview.Enabled = True
Attach.Caption = "Reattach MTR"
Else: Preview.Enabled = False
End If

This just stores a file location into Files, allowing people to continue to edit the fields on the Userform before submitting. The code I posted in the first post is part of the "Submit" function, it is performing last minute operations before entering the data into the sheet.

I have assumed that the user is going to be selecting a file that is not stored in the location I have specified - it is a remote server and the files being attached are most likely local to the machine. I need to be able to move these files onto the remote server if they are not, and move them into the correct location on this server (not to mention rename them using proper conventions).

 

[Engineer Joe]

In your strings I would replace "+" with "&". It may be having a problem with trying to figure out what to do with a string argument with a plus sign. I'm used to seeing "&" to connect string bits. Maybe I'm way off though, just my 2 cents.

 

[Tills13]

In your strings I would replace "+" with "&". It may be having a problem with trying to figure out what to do with a string argument with a plus sign. I'm used to seeing "&" to connect string bits. Maybe I'm way off though, just my 2 cents.

I kind of go back and forth with the plus sign and the ampersand. If what I believe is correct, both are valid string constructors - though in the past, it's kind of been a hit and miss with them (every so often, I'll run into an error using them).