What's wrong? 2

[newguy86]

I am writing a macro through IBM Reflection VBA that utilizes an Excel file to provide the information it needs. And what I am trying to do is from the Reflection Session the macro gets information from an Excel file and inputs it into Reflection and proceeds to perform its various functions. The problem that I am having is that when the macro is done I want it to move to the next cell down and start all over again. In other words I want the macro to move down a list, who's range varies depending on the amount of information. So, what am I doing wrong? I keep trying to do this and have probably come close to the correct answer but everytime try to do this it never works. Either it completly doesn't do what I wanted it to do or I get a varying amount of error messages. I have provided the macro for you to see and make changes to. Also keep in mind that I am fairly new to VBA and some of the jargon is a little foriegn to me. Thanks!

Dim CellContents As String
Dim LocationCount As Integer
Set ExcelApp = Excel.Application
Set ExcelWorkbook = ExcelApp.Workbooks.Open("R:\My Documents\Location Codes To Delete Rates.xls")
LocationCount = 2
StartPos = ExcelApp.ActiveSheets(Sheet1).Range("A & LocationCount")
With Session
If Range("A & LocationCount") <> "" Then
Do While CellContents <> ""
.RunMacro "NewMacros.Location_Code_Input", ""
Let LocationCount = LocationCount + 1
Loop
End If
End With

 

[SkipVought]

Hi,

Dim CellContents As String
Dim LocationCount As Integer
Set ExcelApp = Excel.Application
Set ExcelWorkbook = ExcelApp.Workbooks.Open("R:\My Documents\Location Codes To Delete Rates.xls")
LocationCount = 2
StartPos = [b]ExcelWorkbook.Sheets(Sheet1)[/b].Range("A & LocationCount")
With Session
    If [b]ExcelWorkbook.Sheets(Sheet1)[/b]Range("A & LocationCount") <> "" Then
        Do While [b]ExcelWorkbook.Sheets(Sheet1).Range("A & LocationCount")[/b] <> ""
            .RunMacro "NewMacros.Location_Code_Input", ""
            LocationCount = LocationCount + 1
        Loop
    End If
End With

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[newguy86]

Well it seems to have almost worked but now I get an error message stating "Subscript out of range". For this line:
StartPos = ExcelWorkbook.Sheets(Sheet1).Range("A & LocationCount")

 

[SkipVought]

oops, sorry...

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A & LocationCount")

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[newguy86]

Well I seem to be having a hay-day with this. Now I am getting an "Application-defined or object-defined error" message. I tried to add the following lines to try to satisfy the error.

Dim ExcelApp as Excel.Application
Dim ExcelWorkbook as Excel.Workbook

But the error is still hanging on the same line.

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A & LocationCount")

 

[newguy86]

Here is the full macro again in case you need it.

Dim CellContents As String
Dim LocationCount As Integer
Dim StartPos
Dim ExcelWorkbook As Excel.Workbook
Set ExcelApp = New Excel.Application
Set ExcelWorkbook = ExcelApp.Workbooks.Open("R:\My Documents\Location Codes To Delete Rates.xls")
LocationCount = 2
StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A & LocationCount")
With Session
If StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A & LocationCount") Then
Do While CellContents <> ""
.RunMacro "NewMacros.Location_Code_Input", ""
Let LocationCount = LocationCount + 1
Loop
End If
End With

 

[JustinEzequiel]

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A[red]"[/red] & LocationCount)

 

[newguy86]

Well fry my Motherboard and blow up my hard drive. I thought this was going to be easy.
But seriously, thank you for your input.

 

[SkipVought]

Sorry I missed the errant ".

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[newguy86]

Thats alright Skip. And the macro runs well but the problem now is that when it gets to the end of the sequence and it goes to loop back around it still runs the original cell and doesnt move down to the next cell to run that info through. I have tried bugging it out with the compiler and doing a syntax check but nothing comes back as being invalid or incorrect. I have included the latest macro setup in case you need it.

Dim CellContents As String
Dim LocationCount As Integer
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim StartPos As Boolean
Set ExcelApp = New Excel.Application
Set ExcelWorkbook = ExcelApp.Workbooks.Open("R:\My Documents\Location Codes To Delete Rates.xls")
LocationCount = 2
StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)
CellContents = Range("A" & LocationCount).Value
With Session
If StartPos = True Then
Do While CellContents <> ""
.RunMacro "NewMacros.Location_Code_Input", ""
If CellContents = "" Then Exit Do
Let LocationCount = LocationCount + 1
Loop
End If
End With

One thing I have done is move the LocationCount = LocationCount + 1 over in line with the Loop statement. So we'll see how it goes, but any suggestions would be greatly appreciated.

 

[newguy86]

Update: The problem seems to be that LocationCount is not responding correctly when it reaches the end of the sequence.

 

[SkipVought]

Your cellcontents does not do anything.

    If ExcelWorkbook.Sheets(Sheet1)Range("A" & LocationCount) <> "" Then
        Do While ExcelWorkbook.Sheets(Sheet1).Range("A" & LocationCount) <> ""
            .RunMacro "NewMacros.Location_Code_Input", ""
            LocationCount = LocationCount + 1
        Loop
    End If

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[newguy86]

Ok you are right. When you first gave me that piece of coding it for some reason didn't seem like the syntax was correct. But I still am having the problem with the LocationCount not changing like it should so that when the macro is done with its sequence it knows to go to the next cell down and get the info from there. I have included the updated macro in case anyone needs it.

Dim LocationCount As Integer
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim StartPos
Set ExcelApp = New Excel.Application
Set ExcelWorkbook = ExcelApp.Workbooks.Open("R:\My Documents\Location Codes To Delete Rates.xls")
LocationCount = 2
StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)
With Session
If StartPos <> "" Then
Do While StartPos <> ""
.RunMacro "NewMacros.Location_Code_Input", ""
Let LocationCount = LocationCount + 1
If StartPos = "" Then Exit Do
Loop
End If
End With

Any suggestions would be greatly appreciated.

 

[ck1999]

Should this Also be inside the loop:

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)

ck1999

 

[newguy86]

I don't see why, the line

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)

was to signify that StartPos refers a specific range where Reflection pulls information from and inputs it into the system to process. And that's what the line

Let LocationCount = LocationCount + 1

is for, so that when the process is done the macro adds 1 to LocationCount and loops back around.

If you can show me where you think it should go I will try it and see if it works but based on my logic the current setup seems to be what I need.

 

[SkipVought]

Why do you insist on going back to your original code that is so full of problems, including stuff that does not matter mucking up the situation!!!???

AGAIN, this loop does what I believe you intended...

        Do While ExcelWorkbook.Sheets(Sheet1).Range("A" & LocationCount) <> ""
            .RunMacro "NewMacros.Location_Code_Input", ""
            LocationCount = LocationCount + 1
        Loop

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[newguy86]

I am using this because,

StartPos = ExcelWorkbook.Sheets(Sheet1).Range("A" & LocationCount),

and I will not be the only one who will be looking at this so since StartPos is equal to this line of code it would be easier to understand when someone who didn't write the code is looking at it.

 

[newguy86]

I also wanted to add that I am not really seeing the difference between

Do While StartPos <> ""

and

Do While ExcelWorkbook.Sheets(Sheet1).Range("A" & LocationCount) <> ""

because wouldn't this line make them the same thing?

StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)

 

[SkipVought]

LocationCount = 2
StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)
With Session
    If StartPos <> "" Then
        Do While StartPos <> ""
            .RunMacro "NewMacros.Location_Code_Input", ""
            Let LocationCount = LocationCount + 1[b]
            StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)[/b]
        Loop
    End If
End With

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[newguy86]

One of my co-workers suggested adding in a message box to return the values of StartPos & LocationCount after

Let LocationCount = LocationCount + 1

so that I can see what the values are before it loops and that showed me that LocationCount was actually working correctly but the StartPos was not and so this makes alot more sense. Thank You!