Excel - Data series method fails

gw73

Member
Joined
Dec 15, 2009
Messages
7
Programming Experience
Beginner
Hi
I am very much a part time beginner with vb.net and I am using visual basic 2010 express on a windows xp pro machine. I only have time to work on projects during the fall and winter. I am working on converting an Excel 2007 macro to a stand alone app. This macro works fine in excel so I assume I'm missing something. This sub creates a new worksheet and fills the first two columns with latitude and longitude info that I am using to create a shapefile. I am using long and lat input that the user puts into a textbox, that is limited to 9 digits. These are doubles because I need the precision. I think this has something to do with my problem. When I run the code I get a DataSeries method of Range class failed error. I've had help putting the macro together and I enjoy trying to figure out my problems but I can't figure this one out.

VB.NET:
Private Sub btnCalc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalc.Click
        'create a new excel worksheet
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")


        Dim AStart As Double, AStep As Double
        Dim BStart As Double, BStep As Double
        Dim SeqCount As Integer, i As Integer

        AStart = mTxtBoxLat.Text ' Start value latitude in column A

        AStep = 0.0000763011 'this is about 22 feet 


        BStart = mTxtboxLong.Text' Start value long in Column B
        BStep = -0.000013638 ' this is about 5 feet.      

        SeqCount = 3    ' Number of sequences to increment this will be the number of ranges

        For i = 0 To SeqCount - 1

            With xlWorkSheet.Range("A" & (i * 77) + 1)

                .Value = AStart + (i * AStep)
                .Resize(77).FillDown()

                .Offset(, 1).Value = BStart
                .Offset(, 1).Resize(77).DataSeries(Rowcol:=xlColumns, Type:=xlLinear, Step:=BStep, Trend:=False)

            End With

        Next i

    End Sub

I'm sure I have other problems but this one is giving me a hard time.

Thanks
 
Back
Top