2

I am trying to copy an entire unopened workbook located at one file path, rename it and save it to a new directory... I am having issues with the renaming and saving, here is my code thus far:

Private Sub new_file_Click()
Dim ActBook As Workbook, NewFile As String
Dim ToolFilePath As String, DestFilePath As String, NewToolName As String
NewToolName = InputBox("Enter the Tool Name.")

MsgBox (Tools_ListBox.Value)
ToolFilePath = GetToolPath(Tools_ListBox.Value)
ToolFilePath = GetPath & ToolFilePath

DestFilePath = GetPath & GetDestPath(Tools_ListBox.Value) & NewToolName & ".xlsm"

Set ActBook = Workbooks.Add(ToolFilePath)
ActBook.SaveAs DestFilePath
ActBook.Close True

The file that i want to copy is located at the path in ToolFilePath and the located I want to rename it to is DestFilePath, which includes the excel .xlsm extension.

Where am I going wrong?

Thanks

thebiglebowski11
  • 1,451
  • 10
  • 41
  • 76
  • Under the SaveAs you need the file type added for Excel 2007 and above. – Jon49 Feb 07 '12 at 19:38
  • the file path, DestFilePath has a .xlsm extension – thebiglebowski11 Feb 07 '12 at 19:56
  • What you need is something like this `wkb.SaveAs sFileName, XlFileFormat.xlTemplate`, of course yours will be a different file format, but it should throw an error if you don't have the XlFileFormat specified just appending the file format isn't enough in Excel 2007 and above. The computer I'm working on is Excel 2003 only, otherwise I would get you the correct file type. – Jon49 Feb 07 '12 at 20:07
  • I believe this is the type you are looking for `xlOpenXMLWorkbookMacroEnabled` per http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat(v=office.14).aspx – Jon49 Feb 07 '12 at 20:10

2 Answers2

6

Why not just use:

FileCopy ToolFilePath, DestFilePath

No need to open it.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • okay, so would this work if I had for example ToolFilePath = D:\mytool\thetool.xlsm DestFilePath = d:\copytool\newcopy.xlsm or do I need to make a new file, and the path is just a folder location? – thebiglebowski11 Feb 07 '12 at 20:32
  • I have it copied perfectly and I am watching my two destination variables... Everything looks to be correct, but I receive a file does not exist error. – thebiglebowski11 Feb 07 '12 at 21:21
  • For example: `FileCopy "C:\local files\tester.xlsx", "C:\local files\__TMP\tester_copy.xlsx"` worked for me – Tim Williams Feb 07 '12 at 22:02
2

You can also use the NAME method if you do not wish to retain the original file.

Name ToolFilePath As DestFilePath
Jerry Beaucaire
  • 3,133
  • 16
  • 14