Solved: Invoking the excel saveas screen

**pico** · 12-07-2006, 06:54 AM

Nevermind. Figured it out. Thanks. Works good so far. I had the sheets hidden as veryhidden. I dont think it works with the sheets veryhidden.

**pico** · 12-07-2006, 07:26 AM

Ok Problemos. The code that you supplied works when the sheet is not hidden. As soon as i hide the sheet it wont copy. I tried hiding the sheet in excel and also coding it.

**CBrine** · 12-07-2006, 09:32 AM

So, the sheet is hidden when the code needs to execute? Just add the unhide code to the start of the code.
sheets("NEW_JOB_CODES").visible = false

and hide it again at the end of the code.

sheets("NEW_JOB_CODES").visible = xlsheetveryhidden

HTH
Cal

**pico** · 12-07-2006, 09:58 AM

I tried that and it works. But When i open the saved file again. It updates itlself with the values from the original workbook. I do not want any updating done when i open the saved file. The code right now looks like this

[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
Sheet1.Visible = xlSheetVisible
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
Set pb = Workbooks.Add
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
ws1.Copy pb.Sheets(1)
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
Application.ScreenUpdating = True
'response = "False"
' Do Until response <> "False"
' response = Application.Dialogs(xlDialogSaveAs).Show
' Loop
Application.Dialogs(xlDialogSaveAs).Show
pb.Close False
Sheet1.Visible = xlSheetHidden[/VBA]

**austenr** · 12-07-2006, 10:05 AM

Do you need the code in the saved file for future use? If not you might want to just delete the code in the saved WB. Does it do the updating first thing when you open the newly saved WB? If so move it out of the Workbook Open and put the code in a general module. Just a guess.

**pico** · 12-07-2006, 11:10 AM

No i do not need the code in the saved file. All i need is the values that were copied. Yes it updates as soon as i open the saved file. I dont understand what you mean by move the code from workbook open. The saving is automated. The saved file should have no code and should not update.

**austenr** · 12-07-2006, 11:13 AM

If the saved file updates as soon as you open it then the code may be in the wrong place. Can you post the WB for us to look at? Sanitize any sensitive data first. I suspect that the code is in the wrong module.

**CBrine** · 12-07-2006, 11:35 AM

austenr,
I don't think we are dealing with event level code on his part. It's just a subroutine that he runs to copy the worksheet, and saves it as a different filename.
'course that leads to the question of just what he's talking about when he says he opens the saved copy and it updates to the same values as the other workbook. I'm thinking the issue maybe linked formula's, since the copy he gave me asked to update when I opened it. I might need to do the cell.copy->Vallues as well, after the worksheet copy.

Pico,
If I copy and paste values, you will lose all formula's in the worksheet, is that OK?

Cal

**pico** · 12-07-2006, 11:52 AM

Yup that's fine. I do not need the formulas in the saved file. Just values copied over from the original workbook.

**austenr** · 12-07-2006, 11:53 AM

CB,

OK. Then paste values only should work. That part about when it opens it changes immediatly made me think of Worksheet Change event.

**CBrine** · 12-07-2006, 01:50 PM

pico,
Hopefully this one will fix you up.:-)

[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
Sheet1.Visible = xlSheetVisible
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set pb = Workbooks.Add
ws.Copy pb.Sheets(1)
Set ps = pb.ActiveSheet
ws.Cells.Copy
ps.Cells.PasteSpecial xlPasteValuesAndNumberFormats
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
Sheet1.Visible = xlSheetHidden
End Sub
[/vba]

HTH
Cal

**pico** · 12-07-2006, 04:43 PM

Sorry CBrine. When i mentioned that the worksheet updates itself. I meant the Parts1 sheet updates itself. I was not talking about the Job_Spec_form sheet. The Job spec form sheet copies perfectly with the previous method. Parts1 sheet is the one with the formulas.

**CBrine** · 12-08-2006, 09:14 AM

Pico,
OK, that should be simple as this.

[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
Sheet1.Visible = xlSheetVisible
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
Set pb = Workbooks.Add
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
ws1.Copy pb.Sheets(1)
With pb.VBProject.VBComponents(ps1.CodeName).CodeModule
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ws2.Cells.Copy ps2.Range("A1")
ws2.Cells.Copy
ps2.Cells.PasteSpecial xlPasteValuesAndNumberFormats
ps2.Activate
ActiveWindow.DisplayGridlines = False
Application.ScreenUpdating = True
'response = "False"
' Do Until response <> "False"
' response = Application.Dialogs(xlDialogSaveAs).Show
' Loop
Application.Dialogs(xlDialogSaveAs).Show
pb.Close False
Sheet1.Visible = xlSheetHidden[/vba]

**pico** · 12-11-2006, 06:12 AM

I get an error "pastespecial method of range class failed". Cant really figure out what the problem is. Btw iam using excel 2000.

**CBrine** · 12-11-2006, 08:40 AM

pico,
Can you upload a copy of your sheet? It's going to be difficult to determine what the problem is otherwise?

Cal

**pico** · 12-11-2006, 09:02 AM

I have attached the PARTS1 sheet. The other sheet was included earlier

**pico** · 12-11-2006, 11:07 AM

Ok I came up with some changes to the code. It prints the values that i want in the Parts sheet without formulas. But there are a few changes i need. 1. I'd like the row height of the original copied over to the second sheet. At the moment I tried using the paste special method. It does not seem to work. The column width works perfectly. 2. When i open the saved file i still get the enable macro screen on startup. I thought iam deleting all the code. Just wondering why that's happening.

. Here is the code.

[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
'Sheet1.Visible = xlSheetVisible
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
Set pb = Workbooks.Add
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
ws1.Copy pb.Sheets(1)
With pb.VBProject.VBComponents(ps1.CodeName).CodeModule
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ws2.Range("A1:E10").Cells.Copy ps2.Range("A1:E10")
ws2.Range("A11:E75").Cells.Copy
ps2.Range("A11:E75").Cells.PasteSpecial (xlPasteFormats)
ps2.Range("A11:E75").Cells.PasteSpecial (xlPasteValues)
ps2.Range("A11:E75").Cells.PasteSpecial (8) 'copy column width
ps2.Range("A11:E75").Cells.PasteSpecial (xlrowheight) 'copy row width
ActiveWindow.DisplayGridlines = False
Application.ScreenUpdating = True
'response = "False"
' Do Until response <> "False"
' response = Application.Dialogs(xlDialogSaveAs).Show
' Loop
Application.Dialogs(xlDialogSaveAs).Show
pb.Close False
'Sheet1.Visible = xlSheetHidden[/VBA]

**CBrine** · 12-11-2006, 11:47 AM

I changed the copy and paste method to the entire workbook, added the copy paste values, and setup code to remove the code from the PS2 modules.

[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
'Sheet1.Visible = xlSheetVisible
Set ws1 = wb.Sheets("JOB_SPEC_FORM")
Set ws2 = wb.Sheets("PARTS1")
Set pb = Workbooks.Add
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
ws1.Copy pb.Sheets(1)
With pb.VBProject.VBComponents(ps1.CodeName).CodeModule
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ws2.Copy pb.Sheets(1)
ws2.Range("A11:E75").Cells.Copy
ps2.Range("A11:E75").Cells.PasteSpecial (xlPasteValues)
With pb.VBProject.VBComponents(ps2.CodeName).CodeModule
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
ActiveWindow.DisplayGridlines = False
Application.ScreenUpdating = True
'response = "False"
' Do Until response <> "False"
' response = Application.Dialogs(xlDialogSaveAs).Show
' Loop
Application.Dialogs(xlDialogSaveAs).Show
pb.Close False
'Sheet1.Visible = xlSheetHidden

[/VBA]

Let me know if this works.

Cal

**pico** · 12-11-2006, 02:45 PM

I tried your code. But there seem to be a couple problems there. I have updated it to look like the following . Iam going to attach the saved file. This is what is output after i hit the save button. There are still a couple of problems with this that i cannot seem to fix. It tries to update itself from the original file when i try to open it.2. The copy works but there is an empty sheet in between the Parts1 and teh JobSpecForm sheet. My code looks like this:
[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
Application.ScreenUpdating = False
Set ps1 = pb.Sheets(1)
Set ps2 = pb.Sheets(2)
Sheet1.Visible = xlSheetVisible
ws1.Copy pb.Sheets(1)
Sheet1.Visible = xlSheetHidden
'ws2.Range("A1:E10").Cells.Copy ps2.Range("A1:E10")
ws2.Range("A1:E75").Cells.Copy
ps2.Range("A1:E75").Cells.PasteSpecial (xlPasteFormats)
ps2.Range("A1:E75").Cells.PasteSpecial (xlPasteValues)
ps2.Range("A1:E75").Cells.PasteSpecial (8) 'copy column width
ps2.Rows(2).RowHeight = ws2.Rows(2).RowHeight 'copy 2nd row height
ps2.Rows(4).RowHeight = ws2.Rows(4).RowHeight 'copy 4th row height
ps2.Rows(6).RowHeight = ws2.Rows(6).RowHeight 'copy 6th row height
ps2.Rows(8).RowHeight = ws2.Rows(8).RowHeight 'copy 8th row height
ActiveWindow.DisplayGridlines = False
Application.ScreenUpdating = True
'response = "False"
' Do Until response <> "False"
' response = Application.Dialogs(xlDialogSaveAs).Show
' Loop
Application.Dialogs(xlDialogSaveAs).Show
pb.Close False[/VBA]

This is ****ing me off. Iam getting frustrated with VBA

**pico** · 12-11-2006, 02:47 PM

f

Thread: Solved: Invoking the excel saveas screen

Thread Tools

Display

Posting Permissions