Rename Excel File

[Arran]

Hi,

I was hoping that someone could help me with the "Name" Function in VBA as I
cannot get it to work.
I have got the following code that allows the user to open a workbook (a
form that has been created/completed in a workbook), it then copies certain
cells from the "Form" to the "Data Base".

What I would like to happen next is that when a workbook has been processed
it renames the work book to filename & "Imported" but deletes the original
file.

Option Explicit
Sub importdata()
Dim sFilename As Variant 'could be a boolean (False)
'Dim A As Long
Dim MLRow As Long
Dim NewFormWkbk As Workbook
Dim NewFormWks As Worksheet
Dim DBWks As Worksheet

sFilename = Application.GetOpenFilename

If sFilename = False Then
Exit Sub
End If

Set NewFormWkbk = Workbooks.Open(Filename:=sFilename)

Set NewFormWks = Nothing
On Error Resume Next
Set NewFormWks = NewFormWkbk.Worksheets("New Contract Set Up Form")
On Error GoTo 0

If NewFormWks Is Nothing Then
MsgBox "No sheet named: New Contract Set Up Form"
Exit Sub
End If

Set DBWks = ThisWorkbook.Worksheets("Data Base")

MLRow = 4 'MasterList Start Row

Do Until ThisWorkbook.Sheets("Data Base").Cells(MLRow, 2) = ""
If DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value Then
DBWks.Cells(MLRow, 2).Value = NewFormWks.Cells(5, 7).Value
'Division
DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value
'Contract No

MLRow = 5
Exit Do
End If
MLRow = MLRow + 1
If DBWks.Cells(MLRow, 3).Value = "" Then
DBWks.Cells(MLRow, 2).Value = NewFormWks.Cells(5, 7).Value 'Division
DBWks.Cells(MLRow, 3).Value = NewFormWks.Cells(7, 4).Value 'Contract
No

End If
Loop

Dim OldName As String
Dim OldPath As String
Dim NewName As String

OldPath = NewFormWkbk.Path & "\"
OldName = NewFormWkbk.Name

NewName = OldPath & Left(OldName, Len(OldName) - 4) & "- Imported.xls"
ActiveWorkbook.SaveAs NewName

Kill ("OldPath & Left(OldName, Len(OldName) - 4)")
End Sub

Any help again will be greatly appreciated.

Many thanks

 

[OssieMac]

Hi AAran,

Not positive but I think that you might have 2 .xls in your filename.
However, insert a Stop as per the following in the code and then when it
stops.
Select Debug from the dialog box.
Hover the cursor over the variables Oldpath, Newpath and Newname and see
what their value is. You will have your answer as to what you should do.

Dim OldName As String
Dim OldPath As String
Dim NewName As String

OldPath = NewFormWkbk.Path & "\"
OldName = NewFormWkbk.Name

NewName = OldPath & Left(OldName, Len(OldName) - 4) & "- Imported.xls"

Stop
ActiveWorkbook.SaveAs NewName

The following line is definitely incorrect. You do not enclose variables in
double quotes. Only strings entered as strings go in double quotes. There
should not be any double quotes in that line. Also check in Help but I don't
think there are any parenthesis after Kill. eg Kill "fileName.xls" (if using
an actual string name)

Kill ("OldPath & Left(OldName, Len(OldName) - 4)")

Create a string variable for the filename and do like I said above with a
stop and then see what the string value is.

strFileToKill = OldPath & Left(OldName, Len(OldName) - 4)

Stop

Then when the string is correct.

Kill strFileToKill

 

[Arran]

Hi OssieMac,

Thanks for your help, pointed me in the right direction and came up with the
following code that works perfectly:

Dim OldPath As String
Dim NewName As String

OldPath = NewFormWkbk.Path & "\" & NewFormWkbk.Name

NewName = Left(OldPath, Len(OldPath) - 4) & " - Imported.xls"

ActiveWorkbook.SaveAs NewName

Kill OldPath

Thanks for the tips

Arran