Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-11-21
3
Medium Priority
?
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

618 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