View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Brian Brian is offline
external usenet poster
 
Posts: 683
Default Run Time error on file name

Here is the code to update the Workbook

'Update Engineering Spec Control Button(Sheet 1)
Private Sub Update_Engineer_Spec_10_Click()

With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET")

' Misc Codes:
.Range("L02").Value = Format(Spec_Date_2.Text, "mm-dd-yyyy")
.Range("L03").Value = Me("Distribution_Code_1").Value
.Range("L04").Value = Me("Material_Supplier_1").Value
.Range("L05").Value = Me("Engineering_Supplier_1").Value
.Range("L06").Value = Me("Installation_Supplier_1").Value

Alot more here same as above

End With


'Update Header Footnote Information
Dim sh As Integer

For sh = 1 To Sheets.Count
With Sheets(sh).PageSetup

.LeftHeader = "&8Cilli Code: " & CLLI_Code_1.Value _
& Chr(10) & "Office Name: " & Me.Office_1.Value

.CenterHeader = "&8TEO Number: " & Me.TEO_No_1.Value _
& Chr(10) & "Supplier Order No: " & Me.CES_No_1.Value

.RightHeader = "&8Page &P of &N" & Chr(10) _
& "Appendix No: " & Me.TEO_Appx_No_2.Value

.CenterFooter = "&8RESTRICTED - PROPRIETARY INFORMATION" _
& Chr(10) & "Not for use or Disclosure outside ATT except under
Written Agreement"

.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.55)
.BottomMargin = Application.InchesToPoints(0.7)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = True
.CenterVertically = True
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""

End With

Next sh

'================================================
'Update Data Storage Sheet (Hidden in Job Work Book)
'================================================
With Workbooks("Master Engineering Spec.xlsm").Sheets("JOB DATA")
' Site Information:
.Range("D02").Value = Me("CLLI_Code_1").Value
.Range("D03").Value = Me("Office_1").Value
.Range("D04").Value = Me("Address_11").Value
.Range("D05").Value = Me("Address_12").Value
.Range("D06").Value = Me("City_1").Value
.Range("D07").Value = Me("State_1").Value
.Range("D08").Value = Me("Zip_Code_1").Value

Alot more here same as above

End With

End Sub



"Dave Peterson" wrote:

This line doesn't look right.

..Range("A09").Value = Me("Office_1").Value

Maybe you meant something like:
..Range("A09").Value = Me.controls("Office_1").Value

===========
So somewhere you have a line that looks something like:

With Workbooks("Master Engineering Spec.xlsm")
.saveas filename:=.....
....

Instead of refering to the workbook by its name, you can use a variable. Maybe
even assign the variable when you open the workbook.

dim wkbk as workbook
....
set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm")

Then when you save it...

wkbk.saveas filename:="C:\somenewname"

But keep refering to that variable...

with wkbk.workSheets("COVER SHEET")
.Range("A09").Value = Me.controls("Office_1").Value




Brian wrote:

I have this code when I go to update a new workbook, but once the workbook is
updated and saved, it will not allow you to reupdate it. I think the reason
is because it's looking for the workbook "Master Engineering Spec.xlsm" and
that is not the workbook name.

Here is the sequence I am following.
1: Open User Form
2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8")
3: Fill in the user Form
4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10"
5: Save Engineering Spec workbook (I automaticaly assigns the name.

Now here is where the problem is. You forgot to fill in something on the
User Form. So you go back and put it in, then you click on the Control Button
"Update_Engineer_Spec_10" and you get a run Time Error message.

How can I get around this so that it updates the workbook? Do I need to add
another Control Button to up date exsisting worksbooks?

'Update Engineering Spec Control Button(Sheet 1)
Private Sub Update_Engineer_Spec_10_Click()

With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET")
'Job Address Information
.Range("A09").Value = Me("Office_1").Value
.Range("A10").Value = Me("Address_11").Value
.Range("A11").Value = Me("Address_12").Value
.Range("A12").Value = Me("City_1").Value
.Range("B12").Value = Me("State_1").Value
.Range("C12").Value = Me("Zip_Code_1").Value

More Code Here, but same as above.

End With


--

Dave Peterson
.