Help Please!!

[Jason B]

Hi..
i have some data which is sitting in an array and i want to be able to export it to excel.. Is this possible and how can i do it.. Please help me.. Need this to be done ASAP.. Thanks

Cheers

Jason B.

[PhilipG]

Yes you can do that easily. Most VB books cover interfacing with excel. I don't have any code to show, but you should be able to find the information you need...its out there!

Phil

[Jason B]

hi,
Can some please help me.. Need to be able to export data to an excel spread sheet. If you can could you please write the code to create and excel file so that i can export data to certain rows and columns..

Thanks to everyone for your help!

cheers

Jason B

[BruceG]

Here's your code (I'm assuming your array is 2-dimensional). To run this example, include Microsoft Excel 8.0 Object Library (assuming you have Excel 97) from Project -> References, and stick a command button called cmdMakeXLSpreadsheet onto your form. Paste this code in and run it. The excel file with 3 rows of random numbers will be created in the same directory you run the project from:

Private Sub cmdMakeXLSpreadsheet_Click()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim objRange As Excel.Range
Dim vntDataArray(1 To 3, 1 To 10) As Variant
Dim intX As Integer
Dim intY As Integer
Dim strXLFileName As String

On Error GoTo MakeXLSpreadsheet_Error

' For the sake of the example, fill the array with random data
Randomize
For intX = 1 To 3
For intY = 1 To 10
vntDataArray(intX, intY) = Int(Rnd * 100)
Next
Next

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")

Set objRange = xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(3, 10))

objRange.Value = vntDataArray

strXLFileName = App.Path & "\MyXLFile.xls"

With xlApp
.Cells.Select
.ActiveWorkbook.SaveAs FileName:=strXLFileName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
.Quit
End With

MakeXLSpreadsheet_Exit:
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
Set objRange = Nothing
Exit Sub

MakeXLSpreadsheet_Error:
MsgBox "The following error has occurred:" & vbNewLine _
& Err.Number & " - " & Err.Description, _
vbCritical, "MakeXLSpreadsheet"
Resume MakeXLSpreadsheet_Exit

End Sub