Clicky

First, this is my VERY first attempt at writing a VB form.  I wrote, with the insightful help of many on here, a form in Access to manipulate a file.  Basically what it does is parse a lot of garbage data out and creates a file to import into access 2007, where several queries are run against the data to clean it up for import into a 3rd party accounting software.  Like I said, I have the road map written in Access, and I'm just trying to translate this over, but as I understand it, Access does a lot of the backend work for me already, so I'm hitting some problems with my source code.  My first level code removes unnecessary columns, adds location numbers, and fixes column headers in an excel file.  Currently, my code is sound, right up until my "Delete Totals Line" code, attached below:

Dim srow As Long
        srow = 1

        Do Until srow > xlSheet.Cells.Find(What:="*", SearchDirection:=2, SearchOrder:=1).Row
            If InStr(1, xlSheet.Cells(srow, "B"), "Totals", vbTextCompare) > 0 Then
                xlSheet.Rows(srow).Delete()

            ElseIf xlObj.WorksheetFunction.CountA(xlSheet.Range(srow & ":" & srow)) = 0 Then
                xlSheet.Rows(srow).Delete()
            Else
                srow = srow + 1
            End If
        Loop

This code looks at a file and wherever the word "Totals" appears in column A, it deletes the entire line and shifts up, so as not to leave blank rows.  Basically, it gives me an error about changing from Integer to String not being allowed, but I can't understand why this is an issue, other than it's obviously not allowed.  Please see my FULL code for this first section of events, to see the application.  Basically, I'm looking to fix the code shown above to work in Visual Studio, as opposed to Access 2007.  Any insight would be MOST appreciated.
 Private Sub btnFlatFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFlatFile.Click         Dim xlObj, xlBook, xlSheet         xlObj = CreateObject("Excel.application")         xlBook = xlObj.Workbooks.Open("R:\FTX Import\import.xls")         xlSheet = xlBook.Worksheets(1)          ' Delete top row         Dim wks         For Each wks In xlBook.Worksheets             wks.Rows(1).Delete()         Next wks          ' Delete bottom row         xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).EntireRow.Delete()          ' Delete QOH         xlSheet.Columns(2).Delete()          ' Delete Retail         xlSheet.Columns(2).Delete()          ' Delete Margin         xlSheet.Columns(3).Delete()          ' Insert "Store" column         xlSheet.Columns("A:A").Insert(Shift:=-4161)          ' Insert formula         xlSheet.Range("B2", xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162)).Offset(, -1).Formula = _             "=IF(LEFT(B2,5)=""Total"","""",IF(LEFT(B3,5)=""Total"",VALUE(MID(B3,FIND(""Store"",B3,1)+6,LEN(B3))),A3))"          xlSheet.Range("A2", xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162)).Copy()         xlSheet.Range("A2").PasteSpecial(-4163)          ' Delete "Totals" lines         Dim srow As Long         srow = 1          Do Until srow > xlSheet.Cells.Find(What:="*", SearchDirection:=2, SearchOrder:=1).Row             If InStr(1, xlSheet.Cells(srow, "B"), "Totals", vbTextCompare) > 0 Then                 xlSheet.Rows(srow).Delete()              ElseIf xlObj.WorksheetFunction.CountA(xlSheet.Range(srow & ":" & srow)) = 0 Then                 xlSheet.Rows(srow).Delete()             Else                 srow = srow + 1             End If         Loop          ' Insert Column Headers         xlSheet.Range("A1").Value = "Store"         xlSheet.Range("B1").Value = "Reference"         xlSheet.Range("C1").Value = "Amount"          ' Ending & Saving         xlObj.ScreenUpdating = True          xlObj.ActiveWorkbook.Saved = True         xlObj.DisplayAlerts = False         xlBook.Save()         xlObj.DisplayAlerts = True         xlBook.Close()          xlObj.Quit()          MsgBox("Done", vbOK, "Done")     End Sub                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 

Select allOpen in new window

asked 11/29/2011 05:23

fizzlefry's gravatar image

fizzlefry ♦♦


14 Answers:
You will need to replace the "B" with 2 in this code.
xlSheet.Cells(srow, "B")  => xlSheet.Cells(srow, 2)

Cells() expects numbers not strings.
link

answered

jimthurgood's gravatar image

jimthurgood

Jim...  Thank you so much for the quick response.  I changed my code to reflect what you said:

Dim srow As Long
        srow = 1

        Do Until srow > xlSheet.Cells.Find(What:="*", SearchDirection:=2, SearchOrder:=1).Row
            If InStr(1, xlSheet.Cells(srow, 2), "Totals", vbTextCompare) > 0 Then
                xlSheet.Rows(srow).Delete()

            ElseIf xlObj.WorksheetFunction.CountA(xlSheet.Range(srow & ":" & srow)) = 0 Then
                xlSheet.Rows(srow).Delete()
            Else
                srow = srow + 1
            End If
        Loop

I am still getting the same error though.
link

answered 2011-11-29 at 13:45:06

fizzlefry's gravatar image

fizzlefry

And you get the error on that line of code?
What, exactly, is the error message?
link

answered 2011-11-29 at 14:08:29

jimthurgood's gravatar image

jimthurgood

It's on the line beginning with "If InStr..." so yes...

The error is InvalidCastException was unhandled
Conversion from type "Range" to type "String" is not valid.
link

answered 2011-11-29 at 14:15:59

fizzlefry's gravatar image

fizzlefry

Try this: xlSheet.Cells(srow, 2).value

It works fine without that in excel vba as 'value' is the deafault, but re-reading your message, I'm not clear what you are running this code in.
link

answered 2011-11-29 at 14:21:08

jimthurgood's gravatar image

jimthurgood

This

If InStr(1, xlSheet.Cells(srow, "B"), "Totals", vbTextCompare) > 0 Then

needs to be

If InStr(1, xlSheet.Cells(srow, 2).Value, "Totals", vbTextCompare) > 0 Then

as mentioned above.
link

answered 2011-11-29 at 14:33:36

CodeCruiser's gravatar image

CodeCruiser

And THAT is why you are a missileman!!!  Thanks, man...  Appreciated!!!
link

answered 2011-11-29 at 14:39:51

fizzlefry's gravatar image

fizzlefry

Glad to help :-)

I think @jimthurgood deserved credit as well though.
link

answered 2011-11-29 at 14:50:34

CodeCruiser's gravatar image

CodeCruiser

Damn...  To be honest, I never noticed the usernames (no offense CodeCruiser)...  How could I re-open to allow to give him the assist?
link

answered 2011-11-29 at 15:05:28

fizzlefry's gravatar image

fizzlefry

Use the Request Attention button
link

answered 2011-11-29 at 15:18:02

CodeCruiser's gravatar image

CodeCruiser

I posted the full complete solution to the problem first and I deserve some credit for assisting?
link

answered 2011-11-29 at 15:43:32

jimthurgood's gravatar image

jimthurgood

Jim, I certainly meant no offense in this.  Your solution was definitely posted first, and your first troubleshooting method did not yield a positive result.  When CodeCruiser jumped in, as I said, I really didn't take notice to the username (my fault).  Also reviewing, I see you did include the .value in your response to my error message.  Again, this was an error on my part and I will make sure to correct it.  I appreciate this community for it's intelligence and professionalism, as well as its integrity, and I certainly want to exhibit the same in my conduct.
link

answered 2011-11-30 at 04:51:44

fizzlefry's gravatar image

fizzlefry

Jim, please accept my apologies again for this and above all, I greatly appreciate your help with my problem.  You have my word I will watch more closely as I award points from now on!!!
link

answered 2011-11-30 at 05:24:08

fizzlefry's gravatar image

fizzlefry

Thank you - it's just that it comes after some other strangenesses in here...
link

answered 2011-12-02 at 05:08:22

jimthurgood's gravatar image

jimthurgood

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×19
×33
×37

Asked: 11/29/2011 05:23

Seen: 277 times

Last updated: 12/01/2011 09:08