Solved

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

Posted on 2003-11-21
3
352 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now