Solved

Why does Vb give me an error when I create 2 or more Excel files?

Posted on 2003-11-21
3
356 Views
Last Modified: 2010-05-18
This is for vb6.0!
Ok I need to create two Excel files one right after the other in a functions call. I will display the code later.  But anyway if I comment out either of the calls to make the first not run, the second runs successfully,  If I comment out the call to make the second file not run,  it makes the first makes successfully. However if I leave both in then the system gives this error:

"  Run-Time Error '91'
   object variable or WIth block variable not set
"
it does this for the following command for chalab2
objWorksheet.Name = "Results"

All my variables are local to the sub routians except 1 that works fine and is nothing to do with excel Feel free to copy this into a document all you need on the form is 2  command buttons
one called Command1 and the other called Command2 and 1 text box named Text1.  If you comment either chalab1 or chalab2 it will run fine howere if you them both in if gives me this error
PLEASE HELP ME! I promise to give you an A and all the point to whoever gets the correct answer

Dim aryLines() As String
Private Sub Command1_Click()
chalab1
Chalab2
End Sub

Private Sub Command2_Click()
End
End Sub

Private Sub Form_Load()
Dim i As Integer
i = 1
Text1.Text = Str(i) + " " + Str(i * 23) + vbCrLf
For i = 2 To 64
    Text1.Text = Text1.Text + Str(i) + " " + Str(i * 23) + vbCrLf
Next
End Sub

Function Getline(strq As String) As Integer
Dim intLine As Integer
Dim strLine As String
aryLines = Split(strq, vbCrLf)
For intLine = LBound(aryLines) To (UBound(aryLines) - 1)
  strLine = aryLines(intLine)
Next

Getline = intLine
End Function
Private Sub Chalab2()
  Dim varNum As Long
  Dim x As Integer
  Dim y As Integer
  Dim i As Integer
  Dim j As Integer
  Dim sline As String
  Dim p As String
  y = Getline(Text1.Text)
  Dim objExcel As Excel.Application
  Dim objWorkbook As Excel.Workbook
  Dim objWorksheet As Excel.Worksheet
 
 
  Set objExcel = New Excel.Application
 'Create new workbook
 Set objWorkbook = objExcel.Workbooks.Add()
  objExcel.DisplayAlerts = False
 'Get the first worksheet
 Set objWorksheet = objWorkbook.Worksheets(1)
'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet
'Rename the sheet to Results.
 objWorksheet.Name = "Results" '<-Failes Right here!
   
    'Headers
    objWorksheet.Cells(1, 1) = "Lab 1 Results for " & Date
    objWorksheet.Cells(1, 1).Font.Bold = True
    objWorksheet.Cells(2, 1) = "Percent Output"
    objWorksheet.Cells(2, 1).Font.Bold = True
    objWorksheet.Cells(2, 3) = "lab 1"
    objWorksheet.Cells(2, 3).Font.Bold = True
    objWorksheet.Cells(2, 5) = "Batch Number"
    objWorksheet.Cells(2, 5).Font.Bold = True
    objWorksheet.Cells(3, 1) = " " & Now
    objWorksheet.Cells(3, 1).Font.Bold = True
   
    'objWorksheet.Cells(14, 1) = "Data"
    'objWorksheet.Cells(14, 1).Font.Bold = True
    objWorksheet.Cells(14, 2) = "Percent Of Computers Used %"
    i = 1
    j = 1
  For x = 0 To y - 1
  sline = aryLines(x)
   sline = Trim(sline)
  Do While (Mid(sline, j, 1) <> " ")
    j = j + 1
  Loop
  objWorksheet.Cells((16 + x), 1) = Mid(sline, i, j - 1)
  i = j + 1
  objWorksheet.Cells((16 + x), 2) = Mid(sline, i, Len(sline) - j)
  i = 1
  j = 1
  Next
   p = Trim("A14:B") + Trim(Str(16 + 64))
   Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Results").Range(p), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = objWorksheet.Cells(1, 1) ' Title
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time" ' X-Axis
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percent Of Computers Loged On(%) " ' Y-Axis"
    End With
    ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveSheet.Shapes("Chart 1")
        .Left = 240.75
        .Top = 178.5
        .Width = 1300
        .Height = 500
    End With
   
 objWorkbook.Close True, App.Path & "/Lab2 "
 objExcel.Quit
 Set objExcel = Nothing
 Set objWorksheet = Nothing
 Set objWorkbook = Nothing

 'Text1.Text = ""
End Sub

Private Sub chalab1()
  Dim varNum As Long
  Dim x As Integer
  Dim y As Integer
  Dim i As Integer
  Dim j As Integer
  Dim sline As String
  Dim p As String
  y = Getline(Text1.Text)
  Dim objExcel As Excel.Application
  Dim objWorkbook As Excel.Workbook
  Dim objWorksheet As Excel.Worksheet
 
 
  Set objExcel = New Excel.Application
 'Create new workbook
 Set objWorkbook = objExcel.Workbooks.Add()
  objExcel.DisplayAlerts = False
 'Get the first worksheet
 Set objWorksheet = objWorkbook.Worksheets(1)
'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet
'Rename the sheet to Results.
    objWorksheet.Name = "Results"
   
    'Headers
    objWorksheet.Cells(1, 1) = "Lab 1 Results for " & Date
    objWorksheet.Cells(1, 1).Font.Bold = True
    objWorksheet.Cells(2, 1) = "Percent Output"
    objWorksheet.Cells(2, 1).Font.Bold = True
    objWorksheet.Cells(2, 3) = "lab 1"
    objWorksheet.Cells(2, 3).Font.Bold = True
    objWorksheet.Cells(2, 5) = "Batch Number"
    objWorksheet.Cells(2, 5).Font.Bold = True
    objWorksheet.Cells(3, 1) = " " & Now
    objWorksheet.Cells(3, 1).Font.Bold = True
   
    'objWorksheet.Cells(14, 1) = "Data"
    'objWorksheet.Cells(14, 1).Font.Bold = True
    objWorksheet.Cells(14, 2) = "Percent Of Computers Used %"
    i = 1
    j = 1
  For x = 0 To y - 1
  sline = aryLines(x)
   sline = Trim(sline)
  Do While (Mid(sline, j, 1) <> " ")
    j = j + 1
  Loop
  objWorksheet.Cells((16 + x), 1) = Mid(sline, i, j - 1)
  i = j + 1
  objWorksheet.Cells((16 + x), 2) = Mid(sline, i, Len(sline) - j)
  i = 1
  j = 1
  Next
   p = Trim("A14:B") + Trim(Str(16 + 64))
   Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Results").Range(p), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = objWorksheet.Cells(1, 1) ' Title
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time" ' X-Axis
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percent Of Computers Loged On(%) " ' Y-Axis"
    End With
    ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveSheet.Shapes("Chart 1")
        .Left = 240.75
        .Top = 178.5
        .Width = 1300
        .Height = 500
    End With
   
 objWorkbook.Close True, App.Path & "/Lab1 "
 objExcel.Quit
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing

End Sub


Function ConvertDate(sDate As String) As String
    Dim sMM
    Dim sDD

    If Trim(sDate) = "" Then Exit Function

    sMM = Month(CDate(sDate))
    sDD = Day(CDate(sDate))
    If Len(Trim(sMM)) < 2 Then sMM = "0" & sMM
    If Len(Trim(sDD)) < 2 Then sDD = "0" & sDD
   
    ConvertDate = sMM & "_" & sDD & "_" & Year(CDate(sDate))
End Function




ok I need to get this to work plus its really annoying me that I can't figure it out
Here is the full code.  THanks for the help I know it can be done
0
Comment
Question by:NOT_AN_E-THUG
3 Comments
 

Expert Comment

by:vbhelp12345
ID: 9796098
I see you are ussing my getline function gg,,,Anyhow He is correct about the commententing thing I tried it out If anybody has any ideas I would like to know to!
0
 
LVL 1

Accepted Solution

by:
puneesh earned 125 total points
ID: 9796764
The error you are getting is coz you are using the Properties without qualifying them with object name. ActiveSheet, Charts, ActiveChart, Sheets and other such properties returns Nothing if no WorkSheet/ Workbook is Active. As nothing is active Nothing is returned by these properties.

----------------------------------------------------------------------------------------------------------------------------------------------
Following lines are excerpts from MSDN:

ActiveSheet Property      
Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active. Read-only.

Remarks
If you don’t specify an object qualifier, this property returns the active sheet in the active workbook.
If a workbook appears in more than one window, the ActiveSheet property may be different in different windows.

----------------------------------------------------------------------------------------------------------------------------------------------

HERE IS THE SOLUTION:

Qualify all occurances of ActiveSheet, Charts, ActiveChart, Sheets and other such properties with object name like

Set objWorksheet = objWorkbook.ActiveSheet
objWorkbook.Charts.Add
objWorkbook.ActiveChart.ChartType = xlLineMarkers
objWorkbook.ActiveChart.SetSourceData Source:=objWorkbook.Sheets("Results").Range(p), PlotBy _
    :=xlColumns
objWorkbook.ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
With objWorkbook.ActiveChart
objWorkbook.ActiveSheet.ChartObjects("Chart 1").Activate
With objWorkbook.ActiveSheet.Shapes("Chart 1")

 
0
 

Author Comment

by:NOT_AN_E-THUG
ID: 9800289
I was wondering the command to make collum grpahs intstead of the lines?
objWorkbook.ActiveChart.ChartType = xlLineMarkers is line that needs to be changed
for example objWorkbook.AcobjWorkbook.ActiveChart.ChartType = xcollumgraph
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question