Using Arrays vs copy paste

[mark hansen]

I have a process working in a few areas where multiple people are tasked with reviewing information and entering their evaluation in a spreadsheet. Because more than one person may need to enter this information at the same time, I have them entering their information in a data entry workbook and send that information to a CSV file. The main workbook, that analyzes the compiled information, will read in the CSV file and put it in a table. (The evaluators pull the data entry file from SharePoint, so they can all use the same file at the same time.)

As I have yet be able to figure out how to (more gracefully) append the table with the CSV file, I came upon using this method:
-I have a background worksheet, on the main analytics file, that pulls in the CSV file via a data connection.
-Then I find how many rows came in this time, and copy that area to clipboard.
-Next I find the next available row under the table and paste (special) the information from the background worksheet.
-Then resize the table to include the additional information.
-Pop up a message box telling the user how many new rows were added to the table.

Typically I don't bring in more than 10-20 rows at a time, but at one time I brought in 2000 + rows at one time.

A friend told me rather than using the above steps, I should read the CSV file into an array and print the array to the information directly to the table. In testing, I did get this to work, but it was painfully slow. So slow, when I turned on screen updating, I could see each row go into the table. It wasn't much better when I turned off screen updating. I didn't find a method of directly going to the table. I presume as I wrote the lines of new data one by one, under the table, the table automatically resizing was taking the time. When I pasted the information from the background worksheet, the table didn't resize, until I resized it with VBA code.

I'm still learning how to use arrays, it's not the first method I use to work with data. I am now able to read in the CSV file and put it into a 2D array, but having problems with effectively getting that information into the table.

I guess the question is, with the normal amount of rows in my CSV files, is there any real benefit to using an arrays to capture the data, vs using the method above?
Is there a way to read a CSV file directly into a table I haven't found yet?
Or perhaps is there a more efficient to append a table from an array?

Thanks for any insight provided.

Mark

 

[Marc L]

Is there a way to read a CSV file directly into a table I haven't found yet?

Via the VBA QueryTable for example, samples in every Excel forums …​

Or copy all in a single column then with the Excel feature Text To Column even under VBA …​

Or …​

 

[mark hansen]

Thanks marc. I haven't gotten into power Query because we don't have that available in our company.

 

[Marc L]

​

Not sure if I well catch you but the old QueryTable (exists maybe since last century !) has nothing to do with the new Power Query …​

 

[Akuini]

I am now able to read in the CSV file and put it into a 2D array, but having problems with effectively getting that information into the table.

Actually it's pretty straightforward to return 2D array values to a range.

Here's an example:

Dim va
va = Range("A1:C10")
Range("E1").Resize(UBound(va, 1), UBound(va, 2)) = va

the code loads Range("A1:C10") values to va (2D array) then return it to a range (start at E1).

in your case, return the array to the row below the table, then you can resize the table by using CurrentRegion, providing the table is separated from other data on the sheet. Like this:

With Sheets("Sheet1").ListObjects("Table1")
.Resize .Range.CurrentRegion
End With

And just another idea, but untested:
1. Open the csv file directly in Excel.
2. Copy the content.
3. Go to the table, add 1 blank new row at the bottom.
4. Paste the content to the new row, the table will resize accordingly.