Posts by max_lux

  • Email /Routing Copied Sheet

    Re: Email /Routing Copied Sheet


    Got it - can add the "to" and the "subject line" automatically from cells, though the message has to be typed in


    Code
    Dim mailrecip As String
    mailrecip = ActiveWorkbook.Sheets("Sheet2").Range("G3").Value
Dim mailsub As String
    mailSub = ActiveWorkbook.Sheets("Sheet2").Range("G4").Value


    Application.Dialogs(xlDialogSendMail).Show _
        arg1:=mailrecip, _
        arg2:=mailSub

  • Email /Routing Copied Sheet

    Re: Email /Routing Copied Sheet


    [vba]
    Application.Dialogs(xlDialogSendMail).Show
    [/vba]


    works, sets subject line as well if done after save prior to close


    Anyone know what arguments I may use to set the recipients and message body? Tried what I can think of - doesn't work

  • Email /Routing Copied Sheet

    Re: Email /Routing Copied Sheet


    Heres what I have so far with the following code added into the other code as previously posted.



    Testing...

  • Email /Routing Copied Sheet

    Building upon


    HTML
    http://www.ozgrid.com/forum/showthread.php?t=45139&page=1&pp=20


    Would it be possible to have that 'copied to new book' (sheet2) report sent to email before/ or after it is saved?


    What I have to do currently is save the report, then close the original workbook, go to the generated file, and email it.


    It woul dbe nice to enter an email address, copy and save sheet2, and then send the newly saved sheet2 to email all in one.

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    After actually testing both these procedures in my workbook, I have gone with gmc's. maqpools wouldn't work for me, don't know why, but gmc's does. I made a couple changes to gmccreedy's code though, adding/ changing as follows:
    Added On Error code because sheet was left open if I cancelled - not in test sheet, but in workbook
    Changed dir to c:\, because I think just about everyone has that dir for sure
    Moved one line to make the code more contiguous



    I have included the test workbook for those that wish to see it work.

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    maqpool


    Where were you yesterday lol? hehe thanks for your interest and your help and expertise anyway.


    gmccreedy


    Sweet! :rock: On first try, got error, debug showed me was location error, changed to c:\documents and settings\, and the default excel location displayed when the save as came up. Worked like a charm.


    I did not expect such a present this morning. This is better than anything I got for Christmas! I don't have sugar plums dancing in my head, I wake up thinking code nowadays lol.


    Thank you so very much for your help.

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    Well, 17 hours of this I'm done for the night. Here's where I'm at, not sure if I made any advance. One thing I'm wondering is why I can't (with code) select a cell on sheet2 after:

    Code
    ActiveSheet.Cells.Select 'remove formulas from sheet2 and leave only values
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
    ActiveWorkbook.UpdateLinks = xlUpdateLinksNever 'set links to never


    This may be a part of the problem. I've noticed before that I can't select a cell (range) on a copied sheet after a pastespecial. Below is the full code I am now working with to get what appears to be sheet2 being saved, but actually the original workbook is still saved and sheet2 is left open:


  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    Have tried to switch the window before the save with


    Code
    ActiveWindow.ActivateNext


    and

    Code
    ActiveWindow.ActivatePrevious


    No success.
    I have tried having the cell text taken from sheet2


    Code
    Dim StrName As String
    On Error GoTo InvalidName
    StrName = Sheet2.Range("A1")
    StrName = Application.GetSaveAsFilename(StrName, _
    fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    If StrName = "False" Then Exit Sub
    ThisWorkbook.SaveAs StrName
InvalidName:     MsgBox "The text: " & StrName & _
    " is not a valid file name.", vbCritical, "Filename Error"


    No success.

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    works perfectly for a book if you are saving it as, but if you try save a copied book then it saves your initial book... can't figure out how to do this, and I've been at this for close to 12 hours so I'm gonna give it a rest for awhile.


    Thanks, again, for your help gmccreedy :)

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    This saves, saves correctly, saves as the right file name, and as the correct extension, but still gives the error for some reason... though the error generated contains the entire file path...


    Code
    Dim StrName As String
    On Error GoTo InvalidName
    StrName = Sheet1.Range("A1")
    StrName = Application.GetSaveAsFilename(StrName, _
fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    If StrName = "False" Then Exit Sub
    ThisWorkbook.SaveAs StrName
InvalidName: MsgBox "The text: " & StrName & _
        " is not a valid file name." & Chr(13) & "Filenames may not contain the following characters:" & Chr(13) & "/  \  :  *  ?  <  >  |", vbCritical, "Filename Error"

  • Dim Variable

    Re: Dim Variable


    So it is is loaded as soon as the workbook is? Would it be a good idea, in that case then, to place all dim variables in the workbook object and not in individual modules so they are all pre-loaded?


    Thanks for the answer, btw, Aaron

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    Even closer I think, but it generates the msgBox error


    Code
    Dim FileName As String
    On Error GoTo InvalidName
    FileName = sheet1.Range("A1")
    Application.GetSaveAsFilename (FileName), fileFilter:="Microsoft Excel Workbook (*.xls), *.xls"
    Exit Sub
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name." & Chr(13) & "Filenames (Well Locations) may not contain the following characters:" & Chr(13) & "/  \  :  *  ?  <  >  |", vbCritical, "Filename Error"

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    hehe been playing with this all day, though doesn't work, it's getting close to what I wish to achieve, ie:


    Code
    Dim strName As String
    strName = sheet1.Range("A1")
On Error GoTo InvalidName
    fileSaveName = Application.GetSaveAsFilename((strName), _
    fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name. Filenames (Well Locations) may not contain the following characters:   / \ : * ? < > |", vbCritical, "Filename Error"
Exit Sub

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    it is not saved... goes through the whole action


    because, although i didn't mind, in the previous method, adding the .xls to the filename, and having the filename in quotaion marks ie. "Tacoma_Downtown(2).xls", using the following assigns the filetype, but produces the error


    Code
    Dim strName As String
    strName = sheet1.Range("A1")
On Error GoTo InvalidName
    fileSaveName = Application.GetSaveAsFilename((strName), _
    fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name. Filenames (Well Locations) may not contain the following characters:   / \ : * ? < > |", vbCritical, "Filename Error"
Exit Sub


    Any thoughts on using this?

  • Dim Variable

    Do I have to do this...


    a...every time if used in a private sub?
    b...once if used in public subs?
    c...once only per book?
    d...every time to be safe?

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    As per almost always, one thing leads to another.


    That works perfectly, should the user select ok, but if they cancel, it leaves the copied book open. I can get the active window back to the original book with the following code. I have been trying variations and think these might be the closest, but I'm getting stumped now...


    Code
    If Application.GetSaveAsFilename = False and dlganswer = false Then
    Application.DisplayAlerts = False
    ActiveWindow.Close
End If


    Code
    If dlganswer = False Then
    Application.DisplayAlerts = False
    ActiveWindow.Close
End If


    but nothing I try closes the unsaved copied workbook

  • Save As (Excel Workbook/File as a Cell Text)

    Re: Save As (Excel Workbook/File as a Cell Text)


    such a simple thing, yet overlooked by me, therefore I must say MANY THANKS! You are brilliant!


    It's such a pleasure to have men such as you available to help people like me who have learned everything via the help files (I am assuming you have had some sort of training...).


    Thanks very much gmccreedy!