Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Excel File
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Excel File
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 -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Excel File
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 "OssieMac" wrote: 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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rename excel file name | Excel Discussion (Misc queries) | |||
rename shared Excel file | Excel Discussion (Misc queries) | |||
Rename Excel file via VBA | Excel Programming | |||
Macros in Excel - Save as and rename file | Excel Programming | |||
Macros in Excel - Save as and rename file | Excel Programming |