Declaration error

[stretch66]

Very New to VBA and keep getting a runtime error, can anyone tell me what I am doing wrong please?

Private Sub Command0_Click()

Dim TensorData As Excel.Application
Dim TensorWb As Excel.Workbook
Dim TensorSh As Excel.Worksheet

Set TensorData = New Excel.Application
Set TensorWb = TensorData.Workbooks
Set TensorSh = TensorWb.Worksheets

 

[allan57]

Try:

Set TensorWb = TensorData.Workbooks.Add

 

[stretch66]

Thanks that worked.........though I cannot get my head round "ADD" as I use the variables constantly in the code and so how does "ADD" work?

 

[Bat17]

expression.Add(Template)

it creates a new workbook and TensorWb contains the handle to that book
you can also use a template when you create a new book expression.Add(Template)
you could also open an existing book
Set TensorWb = TensorData.Workbooks.open("path to file")
look in XL help for all the options

Peter

 

[stretch66]

Ok Thanks Peter so I have created a new Worksheet. How do I refer to the Range correctly as I understand if it is not correctly refered to then a memory leak occurs.Private Sub Command0_Click()

Private Sub Command0_Click()

Dim TensorData As Excel.Application
Dim TensorWb As Excel.Workbook
Dim TensorSh As Excel.Worksheet

Set TensorData = New Excel.Application
Set TensorWb = TensorData.Workbooks.Add
Set TensorSh = TensorWb.Worksheets.Add

DoCmd.OpenQuery "qryDeleteTotalHrsTable"

TensorData.Visible = True

TensorData.ActiveWorkbook.SaveAs "C:\Documents and Settings\AdamM\Desktop\Excel Macro\ORR ALL Files\ORR ALL.xls"

'ORR ALL CSV COPY PASTE
ChDir "C:\Documents and Settings\AdamM\Desktop\Excel Macro\ORR ALL Files"
TensorData.Workbooks.Open Filename:= _
"C:\Documents and Settings\AdamM\Desktop\Excel Macro\ORR ALL Files\ORR ALL.CSV"
TensorData.Cells.Select
TensorData.Selection.Copy
TensorData.Windows("ORR ALL.xls").Activate
TensorSh.Range("A1").Select
TensorData.ActiveSheet.Paste
TensorData.Application.CutCopyMode = False
TensorData.ActiveWorkbook.Save

'ORR ALL Manipulation

TensorData.Columns("A:A").Select
TensorData.Selection.Insert Shift:=xlToRight
TensorSh.Range("A1").Select

 

[stretch66]

It's OK I this I got it in the end using:

Set TensorData = New Excel.Application
Set TensorWb = TensorData.Workbooks.Add
Set TensorSh = TensorWb.Worksheets("Sheet1")