List movement in Excel 2

[newguy86]

I am writing a macro that starts in IBM Reflection and pulls information from an Excel file to put into a Reflection Session to process. The problem that I am having is that when the sequence is complete the macro is supposed to add 1 to LocationCount and loop back around to run the sequence again, but when the macro gets to the end it loops back around and the value of LocationCount does not change. I have tried to figure out what is wrong but all the syntax seems correct. Does anyone have any suggestions?

I have included the macro for you to look over.

Dim LocationCount As Integer
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim StartPos As String
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
Do While StartPos <> ""
.RunMacro "NewMacros.Location_Code_Input", ""
Let LocationCount = LocationCount + 1
If StartPos = "" Then Exit Do
Loop
End With

 

[ck1999]

Did you see my post in your other thread

try

Dim LocationCount As Integer
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim StartPos As String
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
    Do While StartPos <> ""

        .RunMacro "NewMacros.Location_Code_Input", ""
       Let LocationCount = LocationCount + 1
StartPos = ExcelWorkbook.Sheets("Sheet1").Range("A" & LocationCount)
 
        If StartPos = "" Then Exit Do
    Loop
End With

ck1999

 

[newguy86]

Yes I did see your other post and this is actually what I asked for from your post. So I will try it and see if it works.

 

[newguy86]

Ok I just ran it again and it did the same thing. Does anyone have any other input?

 

[ck1999]

How does the startpos get used in this macro?

.RunMacro "NewMacros.Location_Code_Input", ""

You need to tell the macro where to start? How is this being accomplished?

If I understand what you are saying and the results you are getting. We need to see the code for this macro

ck1999

 

[newguy86]

StartPos is referencing an excel file that is a simple list used by the macro to copy information from a single cell and paste it into the Reflection Session so that the rest of the sequence can run. The entire macro's purpose is to take a repetitive data entry task and automate it so that it can run on its own without an actual person sitting at a computer typing in the commands. And the only problem with the macro is that once the process gets to the end of said macro it is supposed to loop back around and do it all over again but know to copy the information from the next cell down so that the macro can continue its run.

I am wanting to do it this way so that when the macro is completly finished we will have a list that we can save as completed tasks. But what I am considering, since the information in the Excel file will have to be inputed by an actual person, is to have the macro when its done delete the row that the information was on and shift up the rows so that when the macro loops it will have new information in the same cell to process. I personally would like to have it move down the list without deleting the rows so that if there is a problem or malfunction the macro won't continue to delete the rows and mess up the task.

So again if anyone has any suggestions on how to accomplish this it would be greatly appreciated.

 

[ck1999]

So how does the macro ("NewMacros.Location_Code_Input", "") know what cells in excel to get data from?

Can you post the code for "NewMacros.Location_Code_Input", ""

ck1999

 

[newguy86]

The macro "Location_Code_Input" just basically aligns the cursor to the necessary field, copies the info from Excel and pastes it into the field in IBM Reflection.

I think I now know what you are getting at, Unfortunately I am not at work now and don't have the program to test it. But I think my problem is that when I was first creating the program I used the standard ("A:2") cell positioning so that I could test the process to see if it worked, and just never changed it to be compatible with the new cell position setup. When I go into work tomorrow I will look over that portion of my code and see. And to answer your question about posting the macro, because of a Confidentiality clause, I am forced to be highly conservative with the info that I post so that I don't lose my job. So most parts of the macro were removed so that I could post the problem I was having along with the respective code.

 

[Rofeu]

From what I understand, you're simply trying to loop through all values in column A and do some copying and things.

Instead of using a do loop, I'd first determine the end of the range and then use a for loop, i.e.:

Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim LocationCount As Integer
Const StartPos As Integer = 2
Dim I As Integer

Set ExcelApp = New Excel.Application
Set ExcelWorkbook = ExcelApp.Workbooks.Open("R:\My Documents\Location Codes To Delete Rates.xls")

StartPos = 2
LocationCount = ExcelWorkbook.Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row

With Session
    For I = StartPos To LocationCount
        .RunMacro "NewMacros.Location_Code_Input", ""
    Next
End With

you can pass I to the location_code_input to tell it for which row to get the data from, so no deleting of rows necessary.

Cheers,

Roel