Rename a file before close

[daniels012]

Here is my code. It does everything I need except at the end I cannot figure out how to rename a workbook after I get data from it.

Sub EstimatingData()
    SavedLastProp = MsgBox("If you saved the last Proposal, press Yes, if you are not sure, press NO!", vbYesNo)
    If SavedLastProp = vbNo Then
        Exit Sub
    Else
    End If
    'ClearMerge
        Application.ScreenUpdating = False
    
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim wsSource2 As Worksheet, wsSource3 As Worksheet
    Dim fName As String
    
    ChDrive "C:\"
    ChDir "C:\Documents and Settings\Owner\My Documents\Dropbox\Clients\"
    'ChDir "C:\Clients\"
    
    fName = Application.GetOpenFilename
    On Error GoTo chas
    
    'if this workbook in same dir path, prevent reopen
    If fName = ThisWorkbook.Path & "\" & ThisWorkbook.Name Then
        MsgBox "You have chosen this workbook, choose another.", , "Already Open"
        Exit Sub
    End If
    
    Set wbDest = ThisWorkbook
    Set wsDest = wbDest.Worksheets("EstimatingData")
    Set wbSource = Application.Workbooks.Open(fName)
    Set wsSource = wbSource.Worksheets("Estimating")
    Set wsSource2 = wbSource.Worksheets("Foreman")
    Set wsSource3 = wbSource.Worksheets("Worksheet")
    
    'With wbSource    'Copies the Drawing and Picture tab
    '    .Sheets(Array("Drawing", "Pictures")).Copy _
    '    After:=wbDest(wbDest.Sheets.Count)
    'End If
    
    wsSource.Activate
    wsSource.Range("A1:K97").Copy
    wsDest.Range("a1").PasteSpecial xlPasteValues
    wsDest.Range("a11").Value = fName
    wsSource2.Range("B2:AA56").Copy
    wsDest.Range("n1").PasteSpecial xlPasteValues
    wsSource3.Range("B34").Copy
    wsDest.Range("I18").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    wbDest.Activate
    Range("a1").Select
    wbSource.Close (False)
    
chas:
End Sub

I need right at the end before closing the wbsource, to rename the file with the value in cell U17 Range("U17").value

Thank You for any help,
Michael

 

[daniels012]

And actually, if we could rename the file right before it puts that name in A11 on this line

wsDest.Range("a11").Value = fName

That would be perfect!!!

Thanks again,
Michael

 

[MrKowz]

Which workbook are you wanting to rename? wbsource or wbdest? And which workbook's U17 holds the new filename?

What we'll need to do is save the file as a different name (you can't rename something you currently have open).

 

[JamesW]

Hi,

In order to rename you have to use SaveAs:

wbSource.SaveAs Range("U17").Value
Kill "oldfilename.xls"

Kill will remove the old file.

 

[daniels012]

The source workbook is what we call an "estimating sheet" It's file name is "company name, area of work, date and time" with .xls at the end.
We use this information when we create a proposal.
Which is what all the code does flawlessly.

The code is executed from a button on the "proposal worksheet".
I open the file and it names it fname.
Is there anyway that I can rename it before I open it?
Or after closing it then rename it.

I guess I could do all this manually?

Thank You so far on the help, any further ideas?
Michael

 

[daniels012]

James W
If i use your solution, don't use oldfilename right? I need to know the filename. Can I use kill wbsource? Or Kill Fname?
I am at a loss?

Thank You,
Michael

 

[daniels012]

bump

 

[JamesW]

Not 100% sure, but I think kill wbsource will kill the new file (as you have renamed it).

You could always assign it to another variable just before you save it, like: oldFile = wbsource.name and then kill oldFile.

(Again, not 100% sure if the above will work, I'm a little rusty with this stuff..)

 

[daniels012]

Ok,
I can get a new file with the proper name.

I added this to the code and it works, it creates a file with the new name.

wbSource.SaveAs wsDest2.Range("U17").Value

How do I delete the file from which the SaveAs was created from? Because it leaves 2 files in the Directory now?????

Michael

 

[jmthompson]

Just did something like this myself
untested

Sub EstimatingData()
    SavedLastProp = MsgBox("If you saved the last Proposal, press Yes, if you are not sure, press NO!", vbYesNo)
    If SavedLastProp = vbNo Then
        Exit Sub
    Else
    End If
    'ClearMerge
        Application.ScreenUpdating = False
    
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim wsSource2 As Worksheet, wsSource3 As Worksheet
    Dim fName As String, OldFile as String
    
    ChDrive "C:\"
    ChDir "C:\Documents and Settings\Owner\My Documents\Dropbox\Clients\"
    'ChDir "C:\Clients\"
    
    fName = Application.GetOpenFilename
    On Error GoTo chas
    
    'if this workbook in same dir path, prevent reopen
    If fName = ThisWorkbook.Path & "\" & ThisWorkbook.Name Then
        MsgBox "You have chosen this workbook, choose another.", , "Already Open"
        Exit Sub
    End If
    
    Set wbDest = ThisWorkbook
    Set wsDest = wbDest.Worksheets("EstimatingData")
    Set wbSource = Application.Workbooks.Open(fName)
    Set wsSource = wbSource.Worksheets("Estimating")
    Set wsSource2 = wbSource.Worksheets("Foreman")
    Set wsSource3 = wbSource.Worksheets("Worksheet")
    
    'With wbSource    'Copies the Drawing and Picture tab
    '    .Sheets(Array("Drawing", "Pictures")).Copy _
    '    After:=wbDest(wbDest.Sheets.Count)
    'End If
    
    wsSource.Activate
    wsSource.Range("A1:K97").Copy
    wsDest.Range("a1").PasteSpecial xlPasteValues
    OldFile= wbSource.Name
    wsDest.Range("a11").Value = fName
    wsSource2.Range("B2:AA56").Copy
    wsDest.Range("n1").PasteSpecial xlPasteValues
    wsSource3.Range("B34").Copy
    wsDest.Range("I18").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    wbDest.Activate
    Range("a1").Select
    wbSource.Close (False)
Kill("C:\Documents and Settings\Owner\My Documents\Dropbox\Clients\" & OldFile)
    
chas:
End Sub

</pre>