having save-as problems from autocad to excel...

[btraemoore]

Im trying to saveas the work ive done as a different file name, and i keep getting "script out of range", which i know means that it doesn't exist. I having a hard time figuring out why...

' Declare Working Directory
    Global Const WrkDir = "C:\Documents and Settings\moorerb\Desktop\asset worksheets\"
' Declare Excel Workbook name
    Global Const Master_WorkBook = WrkDir & "bptags.xls"
    Global Const Secondary_WorkBook = WrkDir & "mytemp.xls"
' Declare Excel Worksheet name
    Global Const Master_WorkSheet = "ccu3"
    Global Const Secondary_WorkSheet = "mytemp"
    

Global workbooks As Object
Global ExcelVer As Integer
Global ExcelServer As Object
Global ObjWorksheet As Object
'Global SecWorksheet As Object
'Global ObjWorkbook As Object
Global FileSaveName As String
' end of global variables


Sub RetrieveEXC()
    Set ExcelServer = CreateObject("Excel.Application.11")
        Set workbooks = ExcelServer.workbooks
        workbooks.Add ("C:\Documents and Settings\moorerb\Desktop\asset worksheets\mytemp.xls")
        workbooks.Open (Master_WorkBook)
        
    Set ObjWorksheet = ExcelServer.ActiveWorkbook.worksheets(Master_WorkSheet)
        'ExcelServer.WindowState = -4140
        ExcelServer.Visible = True
        
        FileSaveName = WrkDir & Dwg_Name & ".xls"
        'Set SecWorksheet = ExcelServer.ActiveWorkbook.worksheets(Secondary_WorkSheet)

                    the line below is where my issue lyes.
        workbooks(Master_WorkBook).Sheets(Secondary_WorkSheet).Move After:=workbooks(Secondary_WorkBook).Sheets(1)
        workbooks(Secondary_WorkBook).SaveAs FileSaveName, fileformat:=56
        
        set_to_null
'----------------------------------------------
        ActiveWorkbook.SaveAs FileName:= _
        FileSaveName, fileformat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
'----------------------------------------------        
End Sub

these are my watches

[COLOR=]Watch[/color] : : Master_WorkBook : "C:\Documents and Settings\moorerb\Desktop\asset worksheets\bptags.xls" : String : Exwork
Watch : : Master_WorkSheet : "ccu3" : String : Exwork
Watch : : Secondary_WorkBook : "C:\Documents and Settings\moorerb\Desktop\asset worksheets\mytemp.xls" : String : Exwork
Watch : : Secondary_WorkSheet : "template" : String : Exwork
Watch : : FileSaveName : "C:\Documents and Settings\moorerb\Desktop\asset worksheets\B-4800-E-4419T.xls" : String : Exwork.RetrieveEXC

 

[Andrzejek]

Do you have a worksheet named "mytemp" in your workbook named "mytemp.xls"?

Have fun.

---- Andy

 

[btraemoore]

no the structures are

workbook;worksheet

bptags;ccu3,mytemp
mytemp;sheet1

 

[btraemoore]

so basically

|bptags.xls| |mytemp|
workbooks(Master_WorkBook).Sheets(Secondary_WorkSheet).Move |mytemp.xls| |first sheet|
After:=workbooks(Secondary_WorkBook).Sheets(1)

 

[btraemoore]

sorry i got scrambled


workbooks("bptags.xls").Sheets("mytemp").Move After:=workbooks("mytemp.xls").Sheets(1)"the first sheet