line graph in VB express

[rdischar]

Hello,

I am trying to get vb express 2010 to automatically create an excel sheet and a graph of the data. I recorded a macro of setting up the graph and tried to insert it into vb express but it is giving me some errors on some parts. here is tghe macro I recorded....

Sub INSERTGRAPH()
'
' INSERTGRAPH Macro
'
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveSheet.Shapes("Chart 7").IncrementLeft -290.25
ActiveSheet.Shapes("Chart 7").IncrementTop -195
ActiveSheet.Shapes("Chart 7").ScaleWidth 1.8791666667, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 7").ScaleHeight 3.4652777778, msoFalse, _
msoScaleFromTopLeft
ActiveWindow.SmallScroll Down:=-30
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""X + ON"""
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$E$2:$E$270"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$A$2:$A$270"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""X = OFF"""
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$E$2:$E$270"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!$B$2:$B$270"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""X - ON"""
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!$E$2:$E$270"
ActiveChart.SeriesCollection(3).Values = "=Sheet1!$C$2:$C$270"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "=""X - OFF"""
ActiveChart.SeriesCollection(4).XValues = "=Sheet1!$E$2:$E$270"
ActiveChart.SeriesCollection(4).Values = "=Sheet1!$D$2:$D$270"
End Sub


Here is what my code looks like so far.......

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateExcel.Click
Dim partnum As String = snsrpartnumstr.Text
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim column As Integer = 1
Dim row As Integer = 1
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
　
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
　
' The following statement opens a new sheet and fills in the column headers.
　
xlSheet.Range("A1:H1").Select()
xlSheet.Range("A1:H1").Font.Underline = True
xlSheet.Range("A1:H1").Font.Bold = True
xlSheet.Columns("f:f").ColumnWidth = 17.86
xlSheet.Columns("g:g").ColumnWidth = 22.14
xlSheet.Columns("h:h").ColumnWidth = 23.14
xlSheet.Cells(1, 1) = "X + On"
xlSheet.Cells(1, 2) = "X + Off"
xlSheet.Cells(1, 3) = "X - On"
xlSheet.Cells(1, 4) = "X - Off"
xlSheet.Cells(1, 5) = "Y position"
xlSheet.Cells(1, 6) = "Document Creator :"
xlSheet.Cells(1, 7) = creatortxt.Text
xlSheet.Cells(1, 8) = DateTime.Text

'the following creates a linegraph in the excel sheet'
xlSheet.Shapes.AddChart.Select()
xlSheet.ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
xlSheet.Shapes("Chart 7").IncrementLeft(-290.25)
xlSheet.Shapes("Chart 7").IncrementTop(-195)
xlSheet.Shapes("Chart 7").ScaleWidth( 1.8791666667, msoFalse, msoScaleFromTopLeft)
xlSheet.Shapes("Chart 7").ScaleHeight(3.4652777778, msoFalse, msoScaleFromTopLeft)
xlSheet.ActiveWindow.SmallScroll(Down:=-30)
xlSheet.ActiveChart.SeriesCollection.NewSeries()
xlSheet.ActiveChart.SeriesCollection(1).Name = "=""X + ON"""
xlSheet.ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$E$2:$E$270"
xlSheet.ActiveChart.SeriesCollection(1).Values = "=Sheet1!$A$2:$A$270"
xlSheet.ActiveChart.SeriesCollection.NewSeries()
xlSheet.ActiveChart.SeriesCollection(2).Name = "=""X = OFF"""
xlSheet.ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$E$2:$E$270"
xlSheet.ActiveChart.SeriesCollection(2).Values = "=Sheet1!$B$2:$B$270"
xlSheet.ActiveChart.SeriesCollection.NewSeries()
xlSheet.ActiveChart.SeriesCollection(3).Name = "=""X - ON"""
xlSheet.ActiveChart.SeriesCollection(3).XValues = "=Sheet1!$E$2:$E$270"
xlSheet.ActiveChart.SeriesCollection(3).Values = "=Sheet1!$C$2:$C$270"
xlSheet.ActiveChart.SeriesCollection.NewSeries()
xlSheet.ActiveChart.SeriesCollection(4).Name = "=""X - OFF"""
xlSheet.ActiveChart.SeriesCollection(4).XValues = "=Sheet1!$E$2:$E$270"
xlSheet.ActiveChart.SeriesCollection(4).Values = "=Sheet1!$D$2:$D$270"
　
　
' The following statement shows the sheet.
xlSheet.Application.Visible = True
　
' The following statement saves the sheet to the C:\Test.xls directory.
xlSheet.SaveAs("C:\sensorplots\" & partnum & ".xlsx")

the objects in purple is where my errors are. it is saying that none of them are declared. Do you have any ideas on how to fix this. Thank you for your assistance.

Russell D.

 

[Derek Brown]

Hello and welcome to The Board.
I am not sure of the answer as I would use VB to call a macro in the Excel workbook and not try doing it all from VB - it is much less troublesome.
Try replacing the enum names with values and see if it works.
Try 73 for xlXYScatterSmoothNoMarkers and 0 (zero) for the other two values.
If that works, you can find out the numbers in VBA using something like:

Sub test()
MsgBox xlXYScatterSmoothNoMarkers
End Sub

 

[rdischar]

The 73 did not work in place of the xlxyscaterplotsmooth. I am creating a new form every time I run my VB program right now. I could possibly have a form already set up so I can just put the data I need into it and then save it as something else. Do you know of a good resource for learning about calling macros in excel from VB. Thank you for your assistance.

 

[Derek Brown]

When I get home tonight, I will update this post with the VB code that you need.

 

[Derek Brown]

The following is an example of the VB.net code that I use to manage and update my Data Warehouse. In my partikcular system, this code is called from the Task Scheduler running on a Windows Server - but you do not need to go that far.
In your VB Project you will need to set the project reference to include "Microsoft.Office.Interop.Excel".

Imports System.IO
Module Module1
    Public Sub Main()
        RunMyMacro()
    End Sub
    Sub RunMyMacro()
        Const strControlFileName As String = "X:\Folder1\Folder2\MyWorkbook.xlsm"
        Dim oExcel As Microsoft.Office.Interop.Excel.Application
        Dim oBook As Microsoft.Office.Interop.Excel.Workbook
        Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks
        Dim strFilenameCheck As String
        Dim strMacroName As String
        Try
            strMacroName = "RunMyMacro"
            oExcel = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
            oExcel.Visible = False
            oBooks = CType(oExcel.Workbooks(), Microsoft.Office.Interop.Excel.Workbooks)
            strFilenameCheck = Dir(strControlFileName)
            If strFilenameCheck <> "" Then
                oBook = CType(oBooks.Open(strControlFileName), Microsoft.Office.Interop.Excel.Workbook)
                oExcel.DisplayAlerts = False
                oExcel.Run(strMacroName)
                oExcel.DisplayAlerts = True
            Else
                Dim sw As New StreamWriter(Application.StartupPath & "\MyWorkbook_error.log", True)
                sw.WriteLine(Now() & " - '" & strControlFileName & "' could not be accessed.")
                sw.Close()
                End
            End If
            '
        Catch ex As Exception
            Dim sw As New StreamWriter(Application.StartupPath & "\MyWorkbook_Error.log", True)
            sw.WriteLine(Now() & " - " & ex.Message)
            sw.Close()
        Finally
            oBook.Close(False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
            oBook = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
            oBooks = Nothing
            oExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
        End Try
    End Sub
End Module

You will of course need to change the path, workbookname and macro name.
My processes runs overnight so I have the 'visible' option set to False for the Application, alerts are suppressed and errors will be written to error-logs.