Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 'Save As' cell contents (Excel 2000)

    I have a spreadsheet that is "Read Only" protected and used as a template for other
    spreadsheets. The other (new) spreadsheet will contain a cell that is unique to the
    new spreadsheet. I'd like to create a button (from the 'Control Box' Toolbar ) in the
    template spreadsheet that will create an unprotected new spreadsheet and save
    it using the contents of the unique cell as the file name, then return to the new
    spreadsheet for editing.
    Is this possible ?

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

    Re: Excel 'Save As' cell contents (Excel 2000)

    I'm confused by all these new workbooks:
    <hr>I have a spreadsheet (A) that is "Read Only" protected and used as a template for other
    spreadsheets. The other (new) spreadsheet (B) will contain a cell that is unique to the
    new spreadsheet. I'd like to create a button (from the 'Control Box' Toolbar ) in the
    template spreadsheet that will create an unprotected new spreadsheet (C) and save
    it using the contents of the unique cell as the file name, then return to the new
    spreadsheet (D) for editing.<hr>
    Is C to be created using A as a template, or is it just a blank new workbook?
    Is D = B, or D = C?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'Save As' cell contents (Excel 2000)

    Funny how in one persons mind it can be clear, but when questioned it is murky.

    Spreadsheet (A) is "Read Only" and used as a template for other
    spreadsheets ([img]/forums/images/smilies/cool.gif[/img]. The ([img]/forums/images/smilies/cool.gif[/img] spreadsheet will contain a cell that is unique to the
    ([img]/forums/images/smilies/cool.gif[/img] spreadsheet. I'd like to create a button (from the 'Control Box' Toolbar ) in the
    (A) spreadsheet that will create the ([img]/forums/images/smilies/cool.gif[/img] unprotected spreadsheet and save
    it using the contents of the unique cell as the file name and stay in the ([img]/forums/images/smilies/cool.gif[/img]
    spreadsheet for editing.
    I hope this helps.

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

    Re: Excel 'Save As' cell contents (Excel 2000)

    It's still not clear to me. If ([img]/forums/images/smilies/cool.gif[/img] is to be created from (A), where does the unique value of the cell on ([img]/forums/images/smilies/cool.gif[/img] come from? If you create a new workbook based on (A), it is an identical copy of (A), so how can a value be unique to ([img]/forums/images/smilies/cool.gif[/img]?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'Save As' cell contents (Excel 2000)

    A button (from the ToolBox) in (A) will create a value in (A), save that (A) spreadsheet then,
    create ([img]/forums/images/smilies/cool.gif[/img] and populate a cell in ([img]/forums/images/smilies/cool.gif[/img] with that value. I'm currently working on the macro to
    put the newly created value in the next empty cell in a specific column.
    A couple of years ago I did something very similar to this in QuatroPro. Since then we
    have migrated to MS Office and I'm trying to remove the human-error risk as much as possible.

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

    Re: Excel 'Save As' cell contents (Excel 2000)

    If the unique cell is Sheet1!A1, this simple code would seem to do what you ask:

    Private Sub cmdNew_Click()
    Dim strFilename As String
    strFilename = Worksheets("Sheet1").Range("A1") & ".xls"
    ThisWorkbook.SaveCopyAs Filename:=strFilename
    Workbooks.Open strFilename
    End Sub

    but I fear that's too naive.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'Save As' cell contents (Excel 2000)

    To put "Something" into the first empty cell in column A, use something like this:

    <pre> With Worksheets("Sheet1").Range("A1")
    .Offset(.Range("A1").End(xlDown).Row, 0).Value = "Something"
    End With
    </pre>


    To put "Something" into the cell after the last value in column A, use something like this:

    <pre> With Worksheets("Sheet1").Range("A1")
    .Offset(.Range("A65536").End(xlUp).Row, 0).Value = "My string"
    End With
    </pre>


    To save the file using the name in cell A1, use something like this:

    <pre> If Worksheets("Sheet1").Range("A1").Value = "" Then
    MsgBox "No filename specified in Sheet1 cell A1."
    Exit Sub
    End If
    ThisWorkbook.SaveAs Filename:=Worksheets("Sheet1").Range("A1").Value & ".xls"
    </pre>


    To save a copy of the current file and have both the original and the copy open after the save, use the code that Hans posted.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'Save As' cell contents (Excel 2000)

    Hans & Legare,
    I really appreciate you walking me through this. THANKS.
    But....
    Since starting this task I've been getting an error message indicating that
    VBA6.DLL cannot be found and the macro will not run. When I'm in the VB
    Editor I can run the macro but it appears the macro's will only run from the
    VB Editor screen. Prior to this venture all of the macro's and buttons ran OK.
    I have not added any new programs nor have I downloaded from the
    internet. My virus software is up to date and I've gone through the Excel
    Help Tools "Detect and Repair" process and the MS-Office cd repair
    process to no avail. What did I do to deserve this ????
    What would happen if I deleted the Excel PERSONAL.XLS file ? (just
    a thought).

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

    Re: Excel 'Save As' cell contents (Excel 2000)

    See if this newsgroup post helps, in particular the second problem + solution.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2002
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 'Save As' cell contents (Excel 2000)

    Amazing, simply Amazing.
    That VBA6.DLL fix worked.
    I would think there should/could be some type of cross reference in The Lounge to that fix.
    Simply Amazing what gets answered in the Lounge !
    Care to try and answer this one:
    What are the 6-winning Ohio Lottery Numbers for the upcoming drawing. ?
    Hans / Legare - Thanks ever so much.

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

    Re: Excel 'Save As' cell contents (Excel 2000)

    > What are the 6-winning Ohio Lottery Numbers for the upcoming drawing. ?

    <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

Posting Permissions

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