[RESOLVED] New day, new thread... What's the best way to export arrays into Excel?

[paralyzedcitizen]

I've got a few one dimensional arrays that i want to export into excel so that i can make better graphs. I've got a book which tells me to create an excel object but as usual I've written the code letter for letter and it doesn't work. Is this the best way or is there a better?

[Veritas2.0]

Si_the_geek has a great tutorial on alot of excel related stuff. See specifically Post #6.

[paralyzedcitizen]

That's a really helpful link thank you. But I think I've got something simple wrong somewhere now. At the mo I have this

Private Sub exportoutput_Click()
Dim objexcel As Object
Set objexcel = CreateObject("Excel.sheet")
objexcel.application.Visible = True
objexcel.application.Cells(1, 1).Value = "PWind"
objexcel.application.Cells(1, 2).Value = "PUp"
objexcel.application.Cells(1, 3).Value = "PTraffic"
objexcel.application.Cells(1, 4).Value = "PExit"
objexcel.application.Cells(1, 5).Value = "PBouancy"
objexcel.application.Cells(1, 6).Value = "PDown"
objexcel.application.Cells(1, 7).Value = "PTotal"
For i = 1 To L
    objexcel.Cells((i + 1), 1).Value = PWind(i)
    objexcel.Cells((i + 1), 2).Value = PUp(i)
    objexcel.Cells((i + 1), 3).Value = PTraffic(i)
    objexcel.Cells((i + 1), 4).Value = PExit(i)
    'objexcel.application.cells((i + 1), 5).Value = PBou(i)
    'objexcel.application.cells((i + 1), 6).Value = PDown(i)
    objexcel.Cells((i + 1), 7).Value = PTotal(i)
Next i

End Sub

The error says there is a compiler error and that the sub or function is not defined. It then highlights PWind(i) (first line in the loop).

[westconn1]

you should start by creating an instance of excel, then open a workbook or add a workbook to the workbooks collection then set a sheet object variable in that work book

dim objxl = createobject("excel.application")
set objwb = objxl.workbooks.open("C:\somefolder\someexcelfile.xls")
set objsht = objwb.sheets("mysheet")
with objsht
   .cells(1,1).value = "PWind"
   ' all your other cell values 
end with

your error indicates that the array pwind is not in scope in this sub, so you either need to declare your arrays globally or pass the arrays to the sub when you call the sub

also depending what you want to do you can insert an array into a range. without looping through all the cells
L is probably not valid in your for loop should be a number

[si_the_geek]

Your code to interface with Excel is rather odd, and could have a few issues - I'd definitely recommend using the methods shown in the tutorial instead.

As to the error, it means that as far as this Sub is concerned, the array PWind does not exist. That may be because it is spelt incorrectly, or more likely you declared it somewhere (perhaps inside a different Sub) that is outside of the scope of this routine.

[TysonLPrice]

I've got a few one dimensional arrays that i want to export into excel so that i can make better graphs. I've got a book which tells me to create an excel object but as usual I've written the code letter for letter and it doesn't work. Is this the best way or is there a better?

I don't know if this is in the tutorial or the best way to do it but a program I support does it this way. The array is already populated at this point:

Call .Workbooks.OpenText(strFilename, Origin _
              :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
              xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
              Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
              Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 1), _
              Array(8, 1), Array(9, 2), Array(10, 2), Array(11, 1), Array(12, 1), Array(13, 1), _
              Array(14, 1), Array(15, 1), Array(16, 2), Array(17, 1), Array(18, 1), Array(19, 1), _
              Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
              Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
              Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), _
              Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), _
              Array(44, 2), Array(45, 1), Array(46, 2), Array(47, 2), Array(48, 2), Array(49, 2), _
              Array(50, 2), Array(51, 2), Array(52, 1), Array(53, 2), Array(54, 1), Array(55, 2), _
              Array(56, 1), Array(57, 1), Array(58, 2), Array(59, 1), Array(60, 1), Array(61, 1)))

[paralyzedcitizen]

K, I've started from the beginning using the tutorial.
I've inserted Part A and already have problems in the first line

Private Sub exportoutput_Click()
Dim oXLApp As Excel.Application         'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Set oXLApp = New Excel.Application    'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook
Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet

"user defined type not defined"

[TysonLPrice]

Did you add a reference to Excel?

[paralyzedcitizen]

A reference?

I've been fiddling and I have now managed to get my program to save an excel file to a specific destination with a specific name. Yey!
Trouble is now I'd like to make it complicated and make it save to where i want it under the name i want it saved as. which i appear to now have aswell but... the files that it writes are empty! What's happening, this is my code...

Public Sub exportoutput_Click()
Set objexcel = CreateObject("excel.sheet")
objexcel.application.Visible = False

'For i = 1 To L
'    objexcel.application.cells((i + 1), 1).Value = (i + 1)
'Next i
objexcel.application.cells(1, 1).Value = "42"
' CancelError is True.
On Error GoTo ErrHandler
' Set filters.
CommonDialog4.Filter = "Excel Spreadsheet Files (*.xls)|*.xls"
CommonDialog4.FilterIndex = 4
CommonDialog4.Flags = cdlOFNOverwritePrompt
' Display the Open dialog box.
CommonDialog4.ShowSave
' Call the open file procedure.
Open CommonDialog4.FileName For Output As #4
objexcel.SaveAs CommonDialog4.FileName
ErrHandler:
' User pressed Cancel button.
Exit Sub

objexcel.application.quit
Set objexcel = Nothing
End Sub

[si_the_geek]

The problem is that you are creating the file twice - once with Excel, and once with the Open statement - which you don't need at all (as it will create a text file rather than an Excel file).

FYI, the reason the file is blank is that you are doing an Open but not a Close - which therefore is being done by VB when your program closes (so after Excel has saved the file).

A reference?

Yep, via "Project"->"References", as explained at the start of the tutorial.

It can be removed later (as explained in the "Late Binding" section), but I'd recommend having it while writing/changing code.

[paralyzedcitizen]

aha. Right my program now saves the number "42" to where I want and I've now referenced excel. Unfortunatly when I want to it to save one of my arrays it says the sub or function hasnt been declared, blah blah blah. Am I right in thinking I need to make this variable public? and how do i do that? When I declared the variable it was declared in a different sub but that sub was public, shuldn't thaqt be ok?

[si_the_geek]

No, a variable declared inside a Sub/Function is only available in that particular routine.

To make a variable available to all the routines in a code file (form/module/...) declare it in the General Declarations section (the top of the code file). Ideally you should use the keyword Private rather than Dim (just to make things clearer), but they will have the same effect.

For a better explanation (including Scope as I mentioned earlier), see the article What is the difference between Dim/Private/Public/Global/Static/Const? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

[paralyzedcitizen]

Excellent, all fixed now, thanks everyone for your help