Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compile Error (Excel 2000)

    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 =
    Regards
    Kobus

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error (Excel 2000)

    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.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error (Excel 2000)

    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
    Regards
    Kobus

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error (Excel 2000)

    You can use the following function (originally from <!profile=Don Ceraso>Don Ceraso<!/profile>):

    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
    ...

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Compile Error (Excel 2000)

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error (Excel 2000)

    Hans

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

    Regards
    Regards
    Kobus

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error (Excel 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •