Compile Error (Excel 2000)

Topic

New Reply

Hallo

I am trying to use the following code to break up a workbook and store each sheet as a separate workbook.

Sub BreakItUp()
Dim sht As Worksheet
Dim NFName As String

Const WBPath =

Reply | Quote

Replies

In your post, there are curly quotes instead of straight quotes around C: (and around .xls as well). If they are in your code as well, replace the curly quotes with straight quotes.

Reply | Quote

Hans

Thank you, that solved the problem.

One more question:

What code can I add for a message box to open for input of a new folder name?
This new folder must then be created automatically and the new workbooks stored in this new folder.

I have gone through a lot of the previous posts but can not seem to find something.

Groetnis

Reply | Quote

You can use the following function (originally from Don Ceraso):

Public Function BrowseFolder(Optional Title As String = “Select a Folder”, Optional RootFolder As Variant) As String
On Error Resume Next
BrowseFolder = CreateObject(“Shell.Application”).BrowseForFolder(0, Title, 0, RootFolder).Items.Item.Path
End Function

Copy the function into a module, then use it like this in your code.

Dim WBPath As String
WBPath = BrowseFolder
If WBPath = “” Then
MsgBox “No folder specified.”, vbExclamation
Exit Sub
End If
If Not Right(WBPath, 1) = “” Then
WBPath = WBPath & “”
End If
…

Reply | Quote

Hans,
On my Win2k installation, that will not allow you to create a new folder, only select an existing one. I don’t have WinXP to hand to test, but does it show the New Folder button there?

Reply | Quote

Yes, on my Windows XP there is a “Create new folder” button in the dialog.

Reply | Quote

Hans

Thank you very much for the help, it is much appreciated.

Regards

Reply | Quote