Consulting

Results 1 to 6 of 6

Thread: FileName Manipulation Problem

  1. #1

    Question FileName Manipulation Problem

    hi,

    I have the following code which automatically saves my worksheet into an attachment in an email and autopopulating its filename from values on my worksheet.

    [vba] 'Save the new workbook/Mail it/Delete it
    With ActiveSheet

    TempFilePath = Environ$("temp") & "\"
    TempFileName = Range("C2").Value & ", CONTRACT, " & Format(Range("C22").Value, "dd.mm.yy")[/vba]

    However, the first bit of the filename is from a cell C2 which contains a name e.g. John Smith. However when saving the filename, I want this name to be reversed so would be Smith, John.

    I have some code to do this from another Sub:

    [VBA]Sub SaveToFile()

    Dim FileName As Variant

    FileName = Range("E6").Value
    FileName = Mid(FileName, InStr(FileName, " ") + 1) & " " & _
    Left(FileName, InStr(FileName, " ") - 1) & _
    ", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy")
    FileName = Application.GetSaveAsFilename(FileName, "Microsoft Excel Files (*.xls), *.xls")
    If FileName <> False Then

    ActiveWorkbook.SaveAs FileName

    End If

    End Sub
    [/vba]

    However I tried copying and pasting into my new sub but it doenst like it because it refernces FileName twice....

    any thoughts on how to implement this???

    THanks alot!

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    works ok here but FileName is the name Excel uses as a property - it doesn't seem to matter for me but it would eliminate doubt if you use say MyFileName or TheFileName instead. If hat fails, use different variable names either side of the assignment statement (the '=' sign).

    ps
    Just a suggestion, if there happens to be a name such as
    John Harry Smith
    it currently ends up as
    Harry Smith John
    A small change using instrrev instead of instr may be preferred:
    Mid(FileName, InStrRev(FileName, " ") + 1) & " " & Left(FileName, InStrRev(FileName, " ") - 1)
    which yields
    Smith John Harry
    Last edited by p45cal; 06-25-2009 at 03:54 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    So just change every instance of FileName to MyFileName?

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    As a first step it would be very easy to try it.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Do i need to change this aswell?

    [VBA]Dim FileName As Variant[/VBA]

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    That would seem to fit in with "So just change every instance of FileName to MyFileName"
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •