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" can some one throw me a bone?

' 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

Watch : : 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

[btraemoore]

just to update, this is the working code

'
' work done in excel
'

Sub excelwork()
    Dim i As Integer, n As Long, c As Long
        c = 8
    Dim CurrentItem As String
    Dim pause As Boolean
    Dim match As Variant
    Dim NewSheetName As String
        NewSheetName = Dwg_Name & "assets"
        
    'set a count for items in collection
    For i = 1 To EOC
    Cur_TxtSTR = text_coll(i)
    MasWorksheet.Activate
    MasWorksheet.cells(1, 3).Activate
    
        'set a count for items in excel spreadsheet
        For n = 1 To 876
        CurrentItem = MasWorksheet.cells(n, 3).Value
            
            'compare collection item to each excel item, until we find our match
            If Cur_TxtSTR = CurrentItem Then
                MasWorksheet.Rows(n).Select                ' select the row
                MasWorksheet.Rows(n).Copy                  ' copy the row
                secWorksheet.Activate
                secWorksheet.cells(c, 1).Activate
                secWorksheet.paste                         ' paste the row in the new sheet
                secWorksheet.cells(c, 10).Value = Dwg_Name ' insert the drawing name in new sheet
                c = c + 1
            End If
    
        Next n
        
    Next i
    
    secWorksheet.Copy ' copy the new sheet
    ExcelServer.activeworkbook.sheets("template").Name = NewSheetName ' set the new sheet name
    FileSaveName = ExcelServer.Application.GetSaveAsFilename _
    (InitialFileName:=Dwg_Name & ".xls", Title:="Save As")            ' choose where we're going to save it
    
    If FileSaveName = "False" Then                         ' error handleing
        MsgBox "File not Saved, Actions Cancelled."
        Exit Sub
    Else
        ExcelServer.activeworkbook.SaveAs FileSaveName ' save it
        ExcelServer.activeworkbook.Close               ' close it
    End If
    
    ExcelServer.Application.DisplayAlerts = False ' hide unwanted alerts
    ExcelServer.workbooks("bptags.xls").Close     ' close the work book
    ExcelServer.Quit                              ' quit excel
    set_to_nil                                    ' function to reset object variables to nothing
    
End Sub

This is my blog, fell free to drop by and look or correct
http://showyourcode.blogspot.com/