Vbscript help needed!

Daghs

New member
Joined
Apr 22, 2021
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel10
Hi,

I have a very limited knowledge about vbscript so I am not sure what is possible in vbscript and what is not but I want if my problem could be solved with vbscript code .

The word column I'm using here is just for explaining as we use in excel sheet I dont know how to mention it in .csv way!
The problem I am having is something like this!

I have a file named "list.csv" & a folder named "WORKERS".
Now this folder has .csv files which are generated on daily basis & has just 2 rows of daily wages details.

In "WORKERS" folder file names are listed in list.csv file's first column as serials.

Now what I want is, if "WORKERS" folder file name matches in the first column's row of list.csv "the entire 2nd row of that file to be pasted in list.csv file's 3rd column of matched row"

For example,

List.csv has 2nd row

= "AL,Alex Chris,"

in which AL is a serial & now "WORKERS" folder has matched file named AL.csv with serial so the 2nd row of file AL.csv

= "800,100,200,0,0,80,0"

should be copied in list.csv at the end of the matched row which becomes

= "AL,Alex Chris,800,100,200,0,0,80,0"

If for any reason the file is not generated row must be shown "NOT AVAILABLE"

= "AL,Alex Chris,NOT AVAILABLE"

The way I see it. It's just an act of locating and merging two lines but if somebody has to do it for couple of hundred times daily it becomes an awful job!

I've also attached zip for file and folder. I hope I could get some help here!

Thanks.
 

Attachments

  • Ask.zip
    2.7 KB · Views: 16
No response,
I'm hoping anybody could suggest anything!
 
It's not been 12 hours yet since you first posted AND it's a Sunday! Please be patient. Any help you get will be for free and in our own time. If this is really urgent, you may want to consider looking as a 24/7 paid service, which we don't have here.
 
My apologies for impatience I didn't think of holiday,
I just looked at the views & thought of no response!
 
In the attached is a Power Query suggestion.
on Sheet1 there's a small table where you can update the necessary.
The current data is where things were in my setup, it'll be wrong for you for sure.
You can update these values manually or you can click the buttons to let you navigate to them in a friendlier way. The buttons run a couple of short macros, and these are the ONLY reason you might want to enable macros.
Otherwise, go to the Table1 sheet, right-click somewhere in the table there and choose Refresh. (Alternatively, you can click the Refresh All icon in the Queries & Connections section of the Data tab of the ribbon.)

This table has a query behind it, so to get your file, copy the table (or minus the headers) and paste as values on another sheet or workbook and save as a csv file.
 
Last edited:
Hi,
Thanks for the suggestion I did as you mentioned but I am keep getting an error
"Initialization of the data source failed.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database"

Could you please suggest what am I doing wrong?
 
Could you try again with this attached file; I found a hard-coded reference to a folder which I've taken out now.
 

Attachments

  • ExcelGuru11140.xlsm
    36 KB · Views: 15
I'm sorry but same error again!
I'm using excel2010 this isn't a problem? right!
 
Hi,
Sorry for the delay but I upgraded to excel 2016!
Now here after using navigation buttons when I right click on refresh it gives an error!
"[Expression.Error] The csv.Document parameter 'Columns' is invalid." and I don't have any clue what is that!

Actually I'm new to this power query & my vba programming is also at the beginning level so I hope you wouldn't mind me asking.
Thanks,
 
If it helps locations are,
"D:\WORKERS"
&
"D:\list.csv"
 
This looks like differences in versions. Can you be more specific about where the error is occurring? You might have to open up the interface with a right-click on the table and choose Table|Edit Query
 
Here is the image,
 

Attachments

  • I1.jpg
    I1.jpg
    87.9 KB · Views: 11
  • I2.jpg
    I2.jpg
    86.6 KB · Views: 8
  • I3.jpg
    I3.jpg
    88 KB · Views: 14
  • images.zip
    321.3 KB · Views: 7
Try the attached.
If this also fails, which part of the world are you in (timezone at least) and would you be up for a TeamViewer session? (Google it)
 

Attachments

  • ExcelGuru11140.xlsm
    36.2 KB · Views: 9
Hi,
Again sorry for delay, actually it was some sort of version compatibility issue,
I checked 2019 all of your method files worked fine, the previous versions showed problems anyway,
What you did for me, I am really thankful & I really mean it coz what I was going through before was really stressful!
 
Back
Top