Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 76

Thread: Solved: Invoking the excel saveas screen

  1. #21
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    As always i come back to a solved thread. Instead of invoking the saveas screen can excel just write to the file and stamp the date and time the file was saved on to the filename?. I came across a loophole where a user could hit cancel and the file might not be saved. . The location of the file to be saved is a folder in desktop.

  2. #22
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    pico,
    This will force a save before allowing them to exit. They could most likely ctrl-break out of it, but no other way.

    [vba]
    Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim pb As Workbook, ps1 As Worksheet, ps2 As Worksheet
    Dim s As Shape
    Dim response As String

    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("JOB_SPEC_FORM")
    'Set ws2 = wb.Sheets("PARTS1")
    Set pb = Workbooks.Add
    Set ps1 = pb.Sheets(1)
    Set ps2 = pb.Sheets(2)

    Application.ScreenUpdating = False
    ws1.Cells.Copy ps1.Range("A1")
    ps1.Activate
    ActiveWindow.DisplayGridlines = False
    For Each s In ws1.Shapes
    If s.Type = 12 Then
    s.Copy
    ps1.Paste
    End If
    Next s
    'ws2.Cells.Copy ps2.Range("A1")
    'ps2.Activate
    'ActiveWindow.DisplayGridlines = False
    pb.Activate
    Application.ScreenUpdating = True
    response = "False"
    Do Until response <> "False"
    Application.Dialogs(xlDialogSaveAs).Show
    Loop
    pb.Close False

    Set ps1 = Nothing
    Set ps2 = Nothing
    Set wb1 = Nothing
    Set wb2 = Nothing
    Set pb = Nothing
    Set wb = Nothing
    [/vba]

    PS-If you want to hard code the save path, that is of course possible, but I figure you must have a reason for asking for the dialog originally.

    HTH
    Cal
    The most difficult errors to resolve are the one's you know you didn't make.


  3. #23
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Rats you beat me too it.

  4. #24
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    If i press the save button it saves the file ..but the saveas screen does not close. It keeps looping!

  5. #25
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    pico,
    Forgot to put my variable into the dialog show.

    [VBA]Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim pb As Workbook, ps1 As Worksheet, ps2 As Worksheet
    Dim s As Shape
    Dim response As String

    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("JOB_SPEC_FORM")
    'Set ws2 = wb.Sheets("PARTS1")
    Set pb = Workbooks.Add
    Set ps1 = pb.Sheets(1)
    Set ps2 = pb.Sheets(2)

    Application.ScreenUpdating = False
    ws1.Cells.Copy ps1.Range("A1")
    ps1.Activate
    ActiveWindow.DisplayGridlines = False
    For Each s In ws1.Shapes
    If s.Type = 12 Then
    s.Copy
    ps1.Paste
    End If
    Next s
    'ws2.Cells.Copy ps2.Range("A1")
    'ps2.Activate
    'ActiveWindow.DisplayGridlines = False
    pb.Activate
    Application.ScreenUpdating = True
    response = "False"
    Do Until response <> "False"
    response = Application.Dialogs(xlDialogSaveAs).Show
    Loop
    pb.Close False

    Set ps1 = Nothing
    Set ps2 = Nothing
    Set wb1 = Nothing
    Set wb2 = Nothing
    Set pb = Nothing
    Set wb = Nothing[/VBA]
    The most difficult errors to resolve are the one's you know you didn't make.


  6. #26
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Great it works. Thank you

  7. #27
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Hi CBrine, Hope you are still using this forum
    Got some issues with my code. This is the last code that you supplied. There have been some changes to the form i created. I have added a label with a circle in the jobspec form as well. If you scroll down you will be able to see them when you are in design mode.

    [VBA]Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim pb As Workbook, ps1 As Worksheet, ps2 As Worksheet
    Dim s As Shape
    Dim response As String

    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("JOB_SPEC_FORM")
    Set ws2 = wb.Sheets("PARTS1")
    Set pb = Workbooks.Add
    Set ps1 = pb.Sheets(1)
    Set ps2 = pb.Sheets(2)

    Application.ScreenUpdating = False
    ws1.Cells.Copy ps1.Range("A1")
    ps1.Activate
    ActiveWindow.DisplayGridlines = False
    For Each s In ws1.Shapes
    If s.Type = 12 Then
    s.Copy
    ps1.Paste
    End If
    Next s
    ws2.Cells.Copy ps2.Range("A1")
    ps2.Activate
    ActiveWindow.DisplayGridlines = False
    pb.Activate
    Application.ScreenUpdating = True
    'response = "False"
    'Do Until response <> "False"
    ' response = Application.Dialogs(xlDialogSaveAs).Show
    'Loop
    Application.Dialogs(xlDialogSaveAs).Show
    pb.Close False

    Set ps1 = Nothing
    Set ps2 = Nothing
    Set wb1 = Nothing
    Set wb2 = Nothing
    Set pb = Nothing
    Set wb = Nothing[/VBA]

    I have attached my worksheet as well. When i do the save right now It copies the circles. But they are all the way on top of the copied sheet. Not where i want them to be basically. How can i correct this problem?

  8. #28
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Quote Originally Posted by austenr
    Rats you beat me too it.
    I love this forum....folks trying to be the first with a solution!
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #29
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Can anyone give me a solution to this problem?

  10. #30
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Hey pico,
    I'm assuming you are referring to the groups of 3 circles around
    The most difficult errors to resolve are the one's you know you didn't make.


  11. #31
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Yes Groups of 3 circles. There should be 3 for each floor. They are control toolbox labels

  12. #32
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Pico,
    I'm not sure what is going on it seems like some of the shape objects are corrupted. As soon as I cut and paste them again. The copy seems to work on them? I've attached a copy of the what I did. You are going to need to cut and paste the circle labels back in again.

    HTH
    Cal
    The most difficult errors to resolve are the one's you know you didn't make.


  13. #33
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    I have the same problem. It does not seem to work for some reason. The type that you specify in vba does that include labels as well? s.type=12 .

  14. #34
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Yeah, That's the first thing I checked on. The checkbox's and labels are both type =12.

    When I ran the code on my machine, I had some checkbox's not copy correctly as well.
    Seems pretty strange, I'm not sure what's occurring.
    Have you tried the cut and paste trick I mentioned? The code seems to work after they have been cut and pasted?

    Cal

    PS- I also checked the format, and properties of ones that copied OK vs the ones that didn't and I was not able to see a difference.
    The most difficult errors to resolve are the one's you know you didn't make.


  15. #35
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    I tried using the cut and paste method. It still does not seem to work.

  16. #36
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Ok this is a problem. Instead of saving an excel file i'd like to export the data to a pdf file instead? Is this possible? It does not need to be an excel file the one thats saved. Since this approach is not working i'd like to try exporting my excel sheet to a pdf. How can i achieve this?

  17. #37
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    pico,
    Trying to export to pdf using vba automation, I believe is impossible, since you would need to somehow call the program that convets to pdf. Excel doesn't save to that format natively.
    I'm thinking at this point we may want to go back to the original copy of the entire worksheet, and programmatically remove the code that is transferred with it. I can't do much right now, since I'm at home, but should have some time to look at it on Monday.

    Cal
    The most difficult errors to resolve are the one's you know you didn't make.


  18. #38
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    Ok. Also i wanted to add another variation to the code.[VBA] Copy to the second sheet only if the checkbox is ticked and only if the labels are visible. Else dont copy the object. [/VBA]. I think this might considerably speed up the process as well. I dont need to look at the objects that are not selected. Just thought that i might add that in there. Btw. I dont have a previous copy of the workbook. I have been working with the same workbook since the start. Pretty much overwriting it.

  19. #39
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    pico,
    I made some adjustments and revised the entire code. Now it copies the entire worksheet once again, and it removes the code that is copied with it. Give it a try and let me know.

    [VBA]
    Sub CopyData()
    Dim VBCodeMod As Object, VBComp As Object
    Dim wb As Workbook, ws As Worksheet
    Dim pb As Workbook, ps As Worksheet
    Dim StartLine As Long
    Dim HowManyLines As Long
    Dim response As String
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    Set pb = Workbooks.Add
    ws.Copy pb.Sheets(1)
    Set ps = ActiveSheet
    With pb.VBProject.VBComponents(ps.CodeName).CodeModule
    StartLine = 1
    HowManyLines = .CountOfLines
    .DeleteLines StartLine, HowManyLines
    End With
    response = "False"
    Do Until response <> "False"
    response = Application.Dialogs(xlDialogSaveAs).Show
    Loop
    pb.Close False
    End Sub
    [/VBA]
    The most difficult errors to resolve are the one's you know you didn't make.


  20. #40
    VBAX Contributor
    Joined
    Nov 2006
    Posts
    107
    Location
    I included the second sheet copy in there as well. But there seems to be some problems. The ws.Copy pb.Sheets(1) code does not work. Cant figure out what the problem is.

    [vba]Dim VBCodeMod As Object, VBComp As Object
    Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim pb As Workbook, ps1 As Worksheet, ps2 As Worksheet
    Dim StartLine As Long
    Dim HowManyLines As Long
    Dim response As String
    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("JOB_SPEC_FORM")
    Set ws2 = wb.Sheets("PARTS1")
    Set pb = Workbooks.Add
    ws1.Copy pb.Sheets(1)
    Set ps1 = pb.Sheets(1)
    Set ps2 = pb.Sheets(2)
    With pb.VBProject.VBComponents(ps1.CodeName).CodeModule
    StartLine = 1
    HowManyLines = .CountOfLines
    .DeleteLines StartLine, HowManyLines
    End With
    ws2.Cells.Copy ps2.Range("A1")
    ps2.Activate
    ActiveWindow.DisplayGridlines = False
    'response = "False"
    ' Do Until response <> "False"
    ' response = Application.Dialogs(xlDialogSaveAs).Show
    ' Loop
    Application.Dialogs(xlDialogSaveAs).Show
    pb.Close False
    [/vba]

Posting Permissions

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