vb6/excel questions

[maxtin]

Hi, got 3 questions for handling excel files with vb6...

1-when i already have a excel file that is open and my application try to make a new excel file (with a graph), it fails to build the graph
(Error 27501 Desc: 91 Object variable or With block variable not set)

here's the code:


visual basic code:--------------------------------------------------------------------------------
Private Sub CreateExcelGraphOEE()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheetstat As Excel.Worksheet
Dim iloop As Integer
Dim iColCounter As Integer
Dim sRefExcel As String
Dim sPivotTableSheetName As String

Dim ExcelDLL As CubeExportExcelDll.clsExportExcel
Dim rs As ADODB.Recordset

On Error GoTo CreateExcelGraphOEE_Error

Set ExcelDLL = New CubeExportExcelDll.clsExportExcel
sRefExcel = ""


Set xlApp = ExcelDLL.OpenExcel
Set mvarExcelApplication = xlApp

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Sheets.Add

xlSheet.Name = mvarDataSheetName

xlSheet.Activate
sRefExcel = CVCell(1, 1) 'A1 reference for Title

xlSheet.Range(sRefExcel) = mvarDataSheetName
xlSheet.Range(sRefExcel).Font.Bold = True
xlSheet.Range(sRefExcel).Font.Color = vbBlue

xlSheet.Activate

'Set ExcelDLL = New CubeExportExcelDll.clsExportExcel
ExcelDLL.LetRecordsetData mvarGraphRecordset
sRefExcel = CVCell(2, 1) 'A1 reference for Data
ExcelDLL.DirectExport xlBook.Name, xlSheet.Name, sRefExcel, True, True, False, True

xlApp.Range("F7").Select

xlApp.ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "'" & mvarDataSheetName & "'!R2C1:R" & CStr(mvarGraphRecordset.RecordCount + 2) & "C" & CStr(mvarGraphRecordset.Fields.Count)).CreatePivotTable TableDestination:="", _ TableName:="PivotTable1"

xlApp.ActiveSheet.Name = mvarGraphName

--fails here--> xlApp.ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(mvarGraphRecordset.Fields.Count, 1)

xlApp.ActiveSheet.Cells(mvarGraphRecordset.Fields.Count, 1).Select

xlApp.ActiveSheet.PivotTables("PivotTable1").SmallGrid = False

...
--------------------------------------------------------------------------------

2-i want to know if there's a way to check if a Excel file(s) is(are) already open (and if yes..how to close it(them))??

3-also...how you you specify the excel columns width?

TIA

[RobDog888]

For most basic actions you can find out how its done by recording a macro and seeing how Excel generates the code.

Heres a FAQ of mine on macros - http://vbforums.com/showthread.php?t=402032