Need help to pass values stored in a DataTable into Excel with specific date reference

[skyblue1854]

Hi there,

Let’s say I have the following info stored in a DataTable called dt_data already.

Title	Date	Daily_Price
Copper	23. July 2020	652
Brass MS 58	23. July 2020	514
MP 58	23. July 2020	505
Brass MS 63/37	23. July 2020	545

How to I pass these values into an Excel file so that the result will look like this below?

metal 2020 .xlsx (57.8 KB)

It needs to find the right excel tab (July 2020 in this case), the right column, and the right date.

We can use a fixed column position for each item since Copper will always be in column B, Brass MS 58 in column D, MP 58 in column E, and Brass MS 63/37 in column F.

How do I achieve this without changing the excel format?

[TimK (Tim Kok)]

So for each row in your datatable - you need to first identify the month and year - from your date - in order to select the tab.

You would need to then read that sheet into a datatable without headers in order to work out which row to update.

So in your for each:
WorkSheet = CDate(row.item("Date").ToString).ToString("MMM yyyy") - this will give you for example Jul 2020.
Assign your date in the format like the file:
RowDate = CDate(row.item("Date").ToString).ToString("dd/ MMM/ yy")

As you know the columns for each one as you mentioned you can hard code the column to update but need to identify the row depending on the date for each metal - you can use a switch to assign the column letter:

Switch - row.item("Title").ToString
       - Copper
           - Column = "B"  
       - Brass MS 58
           - Column = "D"

In order to work out the row index
RowIndex = CINT(dtFile.Rows.IndexOf(dtFile.Select("Column0 = '"+strDate+"'")(0)).ToString)+1

You can now write directly to the cell you want to update -

Write Cell
You have your Worksheet variable, and your Column and RowIndex variable.
You Price will be row.item(“Daily_Price”).ToString

Hopefully that makes sense

Try building it out into a sequence, if you get stuck I will be happy to help further

[skyblue1854]

Thank you so much for you help @TimK!

I followed your concept and I think I got it to work. The only problem is that it is running slower than what I was expecting. I have a lot more data that I need to pass to excel than the four sample data that I previously provided. Do you mind if I send you a private message with my sample work so you can help me take a look? Maybe I did something that is causing this. Just curious to see if you know any optimization that can be done.

Thanks a lot!

Respectfully,

[skyblue1854]

I just figured it out. I had an unnecessary loop (For each row in metal 2020.xlsx) inside For each row in dt_data so it was running the RowIndex logic 37 more times for each metal.

[TimK (Tim Kok)]

Glad you caught the cause, good practice debugging and optimising your code