VBA to Import Data from one file to another and transpose paste it.

[chetanojha]

Dear Forum Members,

I have a situation where I receive a lot of files from different sales depots to me on daily basis. These files are identical in nature. All files have three worksheets namely Depot Name, Operator and Order_Details. Only one column (in “Order_Details” worksheet) keeps changing. This “Order_Details” worksheet typically looks like below:

Entity	Description	Values
Deport Name	Name of the Depot	London
Truck Entry	Time when truck entered	01/12/2017 01:45:34
Truck Exit	Time when truck exited	01/12/2017 04:45:34
Total Time Take	Total Time in despatch	03:00:00 Hours
Operator	Name of the Operator	Alan
Operator Average	Average time of the operator	2.45 hours
Total Weight	Weight of the truck when exit	11 Tonnes
Total Sale value	Total Sale Value	$15000
Order Status	Is Order Completed	Completed

<tbody>
</tbody>

Now, I have a master excel sheet (Consolidate_Orders.xls) where I load all this information, received above from Order_Details worksheet, manually at the moment. This Consolidate_Orders.xls workbook looks like below. This workbook consolidates all the files which I receive daily from different depots.


[TABLE="width: 1131"]
<tbody>[TR]
[TD]Deport Name
[/TD]
[TD]Truck Entry
[/TD]
[TD]Truck Exit
[/TD]
[TD]Total Time Taken
[/TD]
[TD]Operator
[/TD]
[TD]Operator Average
[/TD]
[TD]Total Weight
[/TD]
[TD]Total Sale value
[/TD]
[TD]Order Status
[/TD]
[/TR]
[TR]
[TD]London
[/TD]
[TD]01/12/2017 01:45:34
[/TD]
[TD]01/12/2017 04:45:34
[/TD]
[TD]03:00:00 Hours
[/TD]
[TD]Alan
[/TD]
[TD]2.45 hours
[/TD]
[TD]11 Tonnes
[/TD]
[TD]15000
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]Manchester
[/TD]
[TD]01/12/2017 01:45:34
[/TD]
[TD]01/12/2017 04:45:34
[/TD]
[TD]03:00:00 Hours
[/TD]
[TD]Michael
[/TD]
[TD]2.45 hours
[/TD]
[TD]12 Tonnes
[/TD]
[TD]1800
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]Swindon
[/TD]
[TD]01/12/2017 01:45:34
[/TD]
[TD]01/12/2017 04:45:34
[/TD]
[TD]03:00:00 Hours
[/TD]
[TD]Elaine
[/TD]
[TD]2.45 hours
[/TD]
[TD]13 Tonnes
[/TD]
[TD]2000
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]Swansea
[/TD]
[TD]01/12/2017 01:45:34
[/TD]
[TD]01/12/2017 04:45:34
[/TD]
[TD]03:00:00 Hours
[/TD]
[TD]Julie
[/TD]
[TD]2.45 hours
[/TD]
[TD]14 Tonnes
[/TD]
[TD]50000
[/TD]
[TD]Completed
[/TD]
[/TR]
</tbody>[/TABLE]



My requirement is to have a button in the Consolidate_Orders.xls workbook/worksheet – which will then open a dialog box to ask me which file I need to import into the Consolidate_Orders.xls workbook. I will then select London_Depot file (as shown above). Once London_Depot file is selected, data from the worksheet “ORDER_DETAILS” (column name “VALUES” ) will be copied and transpose pasted in the end of the Consolidate_Orders.xls file

I checked this forum and also googled it. There are lot of material.. but I cannot make much sense of it.

Any help would be appreciated.

Thanks a lot

 

[Fluff]

The code you've posted bears no resemblance to what you have asked for, or to what your (in code) comments say.
Therefore can you please supply an accurate sample of your data for all three sheets, along with an accurate description of what you are trying to achieve.

 

[chetanojha]

Hi Fluff,

It is exactly doing the thing which I mentioned in my original thread. The only difference is at one place - i have to paste (transpose) the data after skipping
some columns in the row. Hence you will see something where I pasting data twice in the code.

The final piece of puzzle is when I need to extract the filename based on the string. This needs to be pasted in column A. That is why I have used below code.

[COLOR=#0000ff]'Populate Column A with the name of the file (this should be the string extracted from Fname)"
Sht.Range("A" & Sht.Rows.Count).End(xlUp).Offset(1).Resize(1, 1).Value = _
Fname [/COLOR]

All I need now is to extract the past of the "Fname" and paste it in the Column A of the row. That is what I am trying to do in the above code. Please note - I do not know how to do it and hence I have pasted the variable Fname (which contains the whole directory path of the file).

Hope this will simplify the requirement.

 

[chetanojha]

I tried to add below to the above post but somehow i couldnt edit the post. hence post it as reply..

3. I receive files with three different naming convention. I want to extract string from the filename to identify the from where orders are coming to me.
a. For Filename "ORD Load ORDERS - ORD-DL-LOND-003.xls" -- (If the filename contains DL, I need to extract “ORD-DL-LOND-003” from the filename and put into column A)
b. For Filename "ORD-OL-003 (SWA 10.16).xls" -- (If the filename contains OL, I need to extract “ORD-OL-LOND-003(SWA 10.16)” from the filename and put into column A)
c. For Filename "ORD Load ORDERS - ORD-OFF-002.xls" -- (If the filename contains OFF, I need to extract “ORD-OFF-002” from the filename and put into column A)

Thanks,

 

[Fluff]

Try

   If InStr(Fname, "DL") > 1 Then
      Sht.Range("A" & NxtRw).Value = "DL"
   ElseIf InStr(Fname, "OL") > 1 Then
      Sht.Range("A" & NxtRw).Value = "OL"
   ElseIf InStr(Fname, "OFF") > 1 Then
      Sht.Range("A" & NxtRw).Value = "OFF"
   End If