Sign in to follow this  
Followers 0
RickB75

How to Get the column number in ExcelReadSheetToArray

6 posts in this topic

#1 ·  Posted (edited)

Guys,

Just a quick question. I'm drawing blank on how to get the column number after using ExcelReadSheetToArray. I have the header columns set to specific names in different workbooks in excel. example: Vin, ID, Year, etc...  In some workbooks the vin column could be column 3 and some workbooks it could be in column 7. I know how to do an array search and find the string "Vin" but for the life of me I can't figure out how to get the column number after I find the string. I'm sure this is simple but I'm drawing a complete blank right now! Should I build a loop with a counter and after it finds the string use the position as the Column number? Is there an easier way than building a loop. A simple function to return the column number in the array.

Edited by RickB75

Share this post


Link to post
Share on other sites



I'm not sure I fully understand the problem. But let me give it a try:

An Excel Worksheet consists of rows and columns starting with index 1 for columns and rows.

An array consists of rows and columns starting with index 0 or 1 (if the row/column count is returned in row 0) for rows and index 0 for columns.

So to map the column index of the array to the column number of Excel you simply add 1 to the array index.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Water,

       Thanks for your reply. I apologize for my post sounding confusing.  This is what my goal is. I have multiple excel workbooks with vehicle data in the workbooks. The header (row #1) is dedicated for the vehicle info like Vin #, Make, Model, Trim, etc. In some workbooks the Vin # column could be column 3. In other workbooks it could be column 7. What I'm wanting to do is write a script that can read multiple layouts of different workbooks using the header data as a way to identify the data in the column. Then, I can do a ArraySearch for the word "Vin #" (I know it will show in position 1 because it's always on the 1st row. It's the column number that will vary)  in any one of the workbooks and once I find it, I will know what column all the Vin #'s are in, then I can use the function ExcelReadArray and pass it the column number to read, to get all the Vin #'s in that column.  

 

I guess my main question is can I use a function like ArraySearch to return the column number and the position or do I need to build a loop and use a counter. It seems like I could use ArraySearch and it return the column and position. I just don't know how to get the column number using ArraySearch. I can get the position just not the column number

Edited by RickB75

Share this post


Link to post
Share on other sites

I would read row 1 into an array and then loop through the elements to search for "Vin #".


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Ok, this is what I've found to be the easiest and simplest way for me. Instead of using ExcelReadSheetToArray, I'm using ExcelReadArray and specifying the row number to read and the number of columns to read along with the direction (up or down). This function takes each cells data in the row and places it into its own position in the array. Using ExcelReadSheetToArray, it placed the entire row of data into a single position in the array. Now when I use ArraySearch for my string, It returns the position the string is located in (basically the column number I'm looking for).  No loops. That would be my next attempt to get the column number if this method didn't work.

Thanks for your advice / help Water.

Share this post


Link to post
Share on other sites

Glad to be of service :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0