Manipulating non-Excel file/filenames

[LeoSynapse]

Long time listener, first time caller.

Allow me to set up the scenario for this topic. I am trying to use Excel to make a 10-up ID card template. It works very nicely, but it needs one last touch to make it into a great utility.

I want the user to be able to insert a floppy, with digital camera images, into the floppy drive, and retrieve the images. Once the image has been selected, I want to be able to have Excel rename the image file (.jpg) and save it in a network directory.

So far, I can get the open file dialog to come up, the file can be selected, and the image is inserted into the worksheet. No problem. I can trap the image's original filename in a variable, no problem. I just can't seem to figure out how to bring up the SaveFileAs dialog to allow me to save the image file. I can get the SaveFileAs dialog to open, and I can have it preloaded with the new filename (LName, FName IDNum.jpg), but in the dialog box, the file type remains *.*. If I click OK, nothing actually gets saved.

Is it even possible to perform this action?

Sorry if my posting is poorly written. My experience with VBA is rather short.

Keith

 

[BrianB]

I think you will need to do it this way :-

Sub test()
    Dim NewName As String
    Dim OldName As String
    '----------------------------
    OldName = "A:\something.jpg"
    NewName = Application.GetSaveAsFilename(fileFilter:="jpg Files (*.jpg), *.jpg")
    If NewName <> "False" Then
       FileCopy OldName, NewName
    End If
End Sub

 

[LeoSynapse]

It works perfectly!

Here's the modified code, based on your directions, Brian. The remarks are excessive, but I thought they might help another user.

I totally neglected to add the line: FileCopy OldName, NewName, which is why my save never worked.

Thank you for the assistance.

Keith

----------------------------------------------------------------------------

Sub test()

Dim NewName As String
Dim OldName As String
Dim FileName1 As String

'----------------------------
' Force user to floppy drive
ChDrive ("A\")
OldName = Application.GetOpenFilename(fileFilter:="jpg Files (*.jpg), *.jpg")

' Force user to a specific directory
ChDrive ("C:\test")

' Get file name (FileName1) from user input form in format: LName, FName IDNum
' variables declared elsewhere
FileName1 = "LName" & ", " & "FName" & " " & "IDNum"

' Open Save As dialog with new file name preloaded into File name: field
NewName = Application.GetSaveAsFilename(FileName1, fileFilter:="jpg Files (*.jpg), *.jpg")
If NewName <> "False" Then
FileCopy OldName, NewName
Else: MsgBox "Operation stopped by user. Image not copied.", vbOKOnly, "Stopped!"
End If

End Sub