saving values to excel and back

Hi,

i have the folllowing code to save values to excel

Public strTitleBlockName As String
Public MyTxtStr(0 To 7) As String
Public Cnt, WorkbookOpen, RowCnt As Integer
Public Excel As Excel.Application
Public ExcelSheet, ExcelWorkbook As Object
Public CurrRange As Range
Public myvaratt As Variant


Sub SaveCurrValues()

 If WorkbookOpen = 1 Then GoTo SkipCreatingWorkbook
 
   ' Launch Excel.
   
Set Excel = New Excel.Application
  
   ' Create a new workbook and find the active sheet.
  Set ExcelWorkbook = Excel.Workbooks.Add
   Set ExcelSheet = Excel.ActiveWorkbook.ActiveSheet
   
  ExcelWorkbook.SaveAs "AutoGlassCalcStoredValues.xls", True
  

Excel.Visible = False
 
RowCnt = 1
 
     With Worksheets("Sheet1")
   .Select
   .Range("a1").Activate
       End With

MyTxtStr(0) = XOffset
MyTxtStr(1) = YOffset
MyTxtStr(2) = ScrRef
MyTxtStr(3) = GlassSpec
MyTxtStr(4) = GlassColRef
MyTxtStr(5) = GlassRef
MyTxtStr(6) = TextHeight
MyTxtStr(7) = VPScale
   
SkipCreatingWorkbook:

Cnt = 3
   
    For I = 0 To Cnt
    
    
       Set CurrRange = ActiveCell
           CurrRange.Value = MyTxtStr(I)
               CurrRange.Offset(0, 1).Select
   
   Next
   
   RowCnt = RowCnt + 1
   
    With Worksheets("Sheet1")
   .Select
   .Range("a" & RowCnt).Activate
       End With
   
  WorkbookOpen = 1 'tell the sub that excel has already been opened
  
   ExcelWorkbook.Save
   ExcelWorkbook.Close

  ' Excel.Application.Quit
   'Excel.Application.


End Sub

which works fine

its getting the values back into the acad vba form

i hvae tried the following but it retuns a error of object required??????

Sub LoadCurrValues()


Dim oExcel As Excel.Application
Dim oWB As Workbook


Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open("AutoGlassCalcStoredValues")

Set ExcelSheet = Excel.ActiveWorkbook.ActiveSheet

TxBxXOffset.Value = oWB.Range("a1").Value

TxBxYOffset.Value = Range("b1")
TxBxScrRef.Value = Range("c1")
TxBxSpec.Value = Range("d1")
CboGlsSpcCol.Value = Range("e1")
TxBxRef.Value = Range("f1")
TxBxTHght.Value = Range("g1")
TxBxVPScale.Value = Range("h1")


  
   ExcelWorkbook.Save
   ExcelWorkbook.Close

  ' Excel.Application.Quit
   'Excel.Application.


End Sub



cheers for any help!

col

I played around with it a bit. I am using AutoCAD 2008 & 2010 with Excel 2007. I hope this helps.

Sub LoadCurrValues()

   Dim oExcel As Excel.Application
   Dim oWB As Workbook

   Set oExcel = New Excel.Application
   Set oWB = oExcel.Workbooks.Open("c:\Testing.xls")
   Set Excelsheet = Excel.ActiveWorkbook.ActiveSheet

   TxBxXOffset.Value = Excelsheet.Range("a1").Value
   TxBxYOffset.Value = Excelsheet.Range("b1").Value
   TxBxScrRef.Value = Excelsheet.Range("c1").Value
   TxBxSpec.Value = Excelsheet.Range("d1").Value
   CboGlsSpcCol.Value = Excelsheet.Range("e1").Value
   TxBxRef.Value = Excelsheet.Range("f1").Value
   TxBxTHght.Value = Excelsheet.Range("g1").Value
   TxBxVPScale.Value = Excelsheet.Range("h1").Value

   oWB.Save
   oExcel.Quit

End Sub

Brian,

thank you for your reply.

i have got the following code working fine on Acad2009 with excel 2007 but on acad 2008 excel 2003 it returns the error invalid use of the New Keyword???

Sub LoadCurrValues()

Dim oExcel As Excel.Application
Dim oWB As Workbook

   Dim ExcelSheet As Object
   Dim ExcelWorkbook As Object

   On Error Resume Next

Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open("AutoGlassCalcStoredValues")

oExcel.Visible = False

'Set ExcelSheet = Excel.ActiveSheet

'set oExcel.Range(a1).Value

UFcreateGlassAtt.TxBxXOffset.Value = oWB.ActiveSheet.Range("A1").Value
UFcreateGlassAtt.TxBxYOffset.Value = oWB.ActiveSheet.Range("B1").Value
UFcreateGlassAtt.TxBxScrRef.Value = oWB.ActiveSheet.Range("C1").Value
UFcreateGlassAtt.TxBxSpec.Value = oWB.ActiveSheet.Range("D1").Value
UFcreateGlassAtt.CboGlsSpcCol.Value = oWB.ActiveSheet.Range("E1").Value
UFcreateGlassAtt.TxBxRef.Value = oWB.ActiveSheet.Range("F1").Value
UFcreateGlassAtt.TxBxTHght.Value = oWB.ActiveSheet.Range("G1").Value
UFcreateGlassAtt.TxBxVPScale.Value = oWB.ActiveSheet.Range("H1").Value
  
   oWB.Save
   oWB.Close

   If Err Then Err.Clear

  ' Excel.Application.Quit
   'Excel.Application.


End Sub

From my experience, the reference between Excel 2003 and 2007 are different. I think that if you use CreateObject(“Excel.Application”) it will work with both, but I’m not intirely sure as I no longer have 2003 installed. I did run into this before but I don’t remember how I handled it.

If no one else comes up with an answer before long I will try to dig though some of my older code to find it.

Dim excelObj As Object
   
Set excelObj = CreateObject("excel.Application")
excelObj.Visible = True
   
excelObj.workbooks.Open ("c:\testing.xls")

thanks Brian, i will look thru some of my other codes as i am sure i have done this before with excel and it worked on both 2009 and 2009 acad so im not sure if it is something else that is causing it? i will have to look it out and compare.

cheers,

col

Hi,

i managed to find this example online

for excel 2003

Sub BringToLife()
On Error Resume Next
Dim e As Excel.Application
Set e = New Excel.Application
e.Visible = True
e.Workbooks.Add
e.Worksheets(“Sheet1”).Cells(4, 4).Value = 256
If Err Then MsgBox Error$
End Sub

an to me it looks as tho i am doing it correctly? their must be something else causing the error but for some reason is coming back an error on the "New Excel.Application" part of the code...?

cheers,

col