Solved Control Excel From Access

[GC2010]

I have scenario where on my Excel Worksheet I have IP Addresses so something like
192.168.5.110
169.222.2.210
300.111.43.810

etc etc. What I need is access vba that will assign a alphabetical value to each one and write that value down each cell. (Yes there will be duplicates in the IP list)
192.168.5.110 A
169.222.2.210 B
300.111.43.810 C
192.168.5.110 A
169.222.2.210 B
300.111.43.810 C
192.168.5.110 A
169.222.2.210 B
300.111.43.810 C

like the above. IP address would be in column D and alphabet value would be in column E. I know how to control Excel from Access, I just do not know how to take on this task.

 

[theDBguy]

Hi. I don't do much Excel; but just wondering, could this be done using a formula?

 

[GC2010]

possibly. I know if I was going to do it straight in Excel - I would copy the list to a new worksheet, remove duplicates, then assign a alphabetical letter to each unique. Then I'd do a VLookup() and pull over the assigned alphabetical letter.

I was thinking maybe read the column into an array, remove dupes, then assign a alphabetical letter. But that may not be the best approach.

 

[theDBguy]

possibly. I know if I was going to do it straight in Excel - I would copy the list to a new worksheet, remove duplicates, then assign a alphabetical letter to each unique. Then I'd do a VLookup() and pull over the assigned alphabetical letter.

I was thinking maybe read the column into an array, remove dupes, then assign a alphabetical letter. But that may not be the best approach.

Oh, so there's no function like ConvertToLetter() where if you had a value of 1, 2, or 3 that it would convert them to A, B, and C? Is the reason why you want to do this in Access is because the Worksheet can change each time?

 

[MajP]

Not sure why you need Access. Sort the worksheet by IP address. Take the range and read it from top to bottom. Start at ASCII (N) = 65 which is A and if the IP changes the new ASCII is N + 1. So what is the question?

 

[GC2010]

Oh, so there's no function like ConvertToLetter() where if you had a value of 1, 2, or 3 that it would convert them to A, B, and C? Is the reason why you want to do this in Access is because the Worksheet can change each time?

Not that I know of. Doing it via acmess as we are using a form to allow the user to select the workbook then the data manipulation is done behind the scenes and will save the workbook in the format that they need it in.

 

[GC2010]

Not sure why you need Access. Sort the worksheet by IP address. Take the range and read it from top to bottom. Start at ASCII (N) = 65 which is A and if the IP changes the new ASCII is N + 1. So what is the question?

I need access as we are using an access form for the user to select the workbook then using access vba to control excel to format the workbook in the way that we need.

 

[MajP]

I need access as we are using an access form for the user to select the workbook then using access vba to control excel to format the workbook in the way that we need

So you only want to manipulate the Excel and not import anything? Does not really matter. Either way sort the worksheet. Read from top to bottom. If the current IP changes increment the ID using the ASCII code. If you do it externally or internally the procedure is the same.

 

[GC2010]

I see. How would I read the ASCII code so that if the IP address changes increment form A - B - C - D etc

 

[Pat Hartman]

I would make a lookup table to convert from numbers to letters. 1-26 = A-Z, 27-52 = AA - AZ, keep going until you have created enough entries to handle all expected entries. Make sure to add code to identify numbers higher than you have accounted for. You can do the conversion with VBA if you prefer but the lookup table is pretty simple.

When I need to automate Excel and I don't know the vba, I open the spreadsheet in Excel and turn on the macro recorder. Then I try to perform the action using Excel. At the end, turn off the recorder and open up the macro to see the VBA it generated. You can copy the code into Access with minor changes. Basically, you need to understand that the generated references are "internal, Excel to Excel" and you might need to convert them to "external - Access to Excel).

 

[June7]

Pat got in first with what I was going to suggest. More about referencing: recorder will generate code using Active<thing>. In Access automation procedure will need to declare/set/reference object variables and replace the Active* referencing.

 

[Isaac]

replace the Active* referencing

Excellent tip ... and I would strongly add to that, that "Active", "Activate", "Select" should never be used in Excel VBA. (extra FYI).