Link to home
Start Free TrialLog in
Avatar of Chrisneilhopkins

asked on

VB Script help

Hi, i have the following script which isn't working. Can someone please help me in finding the problem...

 User generated image
The script works for the first and last line (Which ive highlighted in red), but misses everything inbetween.
Can someone please tell me what i have done wrong?
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Given that you're overwriting arrFileNames each time, what are you actually expecting to happen?

Avatar of Chrisneilhopkins


Hi Chris,

I am not very good at VB Scipt at all. I am trying to create folders for all the names entered on all lines. Do i need
arrFileNames = Array on each line or not? at the moment the script creates folders for the names in the first and last line, but i want it to create folders for all of the lines.


Hmmm well, it should only do anything at all for the last line. But I'm making assumptions, can you post your script?

Here is the script,
Apologies, it doesn't seem to be pasting correctly so i have also attached it as a txt file...
' This is the root folder where the individual customer folders are,
' and is also the same folder where the folders will be created
' for each of the file names
strRootFolder = "C:\Dump\Qlikview\Retail\Source"
' This is the list of files in each folder that will be moved to the
' new folders in the root folder above
arrFileNames = Array("ProductSale.csv", "POSTransactionHeader.csv", "POSTransactionDetail.csv", "AgeCheckSale.csv", "CashierDiscount.csv") 
arrFileNames = Array("CashierPayment.csv", "CashierSale.csv", "CashierTender.csv", "DealCheckHead.csv", "DealCheckLine.csv", "DealHeader.csv")
arrFileNames = Array("DealProductLine.csv", "DealReportItem.csv", "DealReportList.csv", "DealReportMain.csv", "DealReportTransaction.csv")
arrFileNames = Array("DealSale.csv", "DepartmentSale.csv", "EFTCardSale.csv", "GoodsReceivedNotesDetail.csv", "GoodsReceivedNotesHeader.csv") 
arrFileNames = Array("OrderHeader.csv", "POSPaymentTransaction.csv", "POSTransactionVAT.csv", "PriceReductionLine.csv", "StoreCommodityGroup.csv") 
arrFileNames = Array("StoreDepartment.csv", "StoreEmployee.csv", "StoreFinancialStatistic.csv", "StoreIncidentDetail.csv", "StoreProduct.csv")
arrFileNames = Array("HHUGapCheck.csv", "HourlySale.csv", "OrderDetail.csv", "StoreProductIngredients.csv", "StoreProductStock.csv", "StoreReasonCode.csv")
arrFileNames = Array("StoreStockMovement.csv", "StoreStockOut.csv", "StoreStockTransfer.csv", "StoreSupplier.csv", "StoreSystemDetail.csv")
arrFileNames = Array("StoreVATRate.csv", "TillPayment.csv", "TillTender.csv", "VATSale.csv")

' First check if the folders for each file name exist to copy them into
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Right(strRootFolder, 1) = "\" Then strRootFolder = Left(strRootFolder, Len(strRootFolder) - 1)
For Each strFileName In arrFileNames
	If objFSO.FolderExists(strRootFolder & "\" & Left(strFileName, InStr(strFileName, ".") - 1)) = False Then
		objFSO.CreateFolder strRootFolder & "\" & Left(strFileName, InStr(strFileName, ".") - 1)
	End If

' Now go through the customer folders
For Each objSubFolder In objFSO.GetFolder(strRootFolder).SubFolders
	For Each strFileName In arrFileNames
		strNewName = strRootFolder & "\" & Left(strFileName, InStr(strFileName, ".") - 1) & "\" & objSubFolder.Name & strFileName
		If objFSO.FileExists(objSubFolder.Path & "\" & strFileName) = True Then
			If objFSO.FileExists(strNewName) = True Then objFSO.DeleteFile strNewName, True
			objFSO.MoveFile objSubFolder.Path & "\" & strFileName, strNewName
		End If

MsgBox "Done" 

Open in new window

Hmm, seems to have pasted ok actually. :)
I've dumped it into a code block for you, the CODE button on the formatting bit is very useful :)

So, I would only expect the script to do anything for the last set of file names, that's what you have left in arrFileNames at the end of the first block. Do you want arrFileNames to include every single one of the listed names?

If so, you want this:
arrFileNames = Array("ProductSale.csv", "POSTransactionHeader.csv", "POSTransactionDetail.csv", _
  "AgeCheckSale.csv", "CashierDiscount.csv", "CashierPayment.csv", "CashierSale.csv", "CashierTender.csv", _
  "DealCheckHead.csv", "DealCheckLine.csv", "DealHeader.csv", "DealProductLine.csv", "DealReportItem.csv", _
  "DealReportList.csv", "DealReportMain.csv", "DealReportTransaction.csv", "DealSale.csv", "DepartmentSale.csv", _
  "EFTCardSale.csv", "GoodsReceivedNotesDetail.csv", "GoodsReceivedNotesHeader.csv", "OrderHeader.csv", _
  "POSPaymentTransaction.csv", "POSTransactionVAT.csv", "PriceReductionLine.csv", "StoreCommodityGroup.csv", _
  "StoreDepartment.csv", "StoreEmployee.csv", "StoreFinancialStatistic.csv", "StoreIncidentDetail.csv", "StoreProduct.csv", _
  "HHUGapCheck.csv", "HourlySale.csv", "OrderDetail.csv", "StoreProductIngredients.csv", "StoreProductStock.csv", _
  "StoreReasonCode.csv", "StoreStockMovement.csv", "StoreStockOut.csv", "StoreStockTransfer.csv", "StoreSupplier.csv", _
  "StoreSystemDetail.csv", "StoreVATRate.csv", "TillPayment.csv", "TillTender.csv", "VATSale.csv")

Open in new window

That way, all of the file names are stored in a single array, rather than it overwriting each time.

All _ does it let us carry the command on over multiple lines. There so my eyes don't hurt :)

Ahhh,  I tried to get it working on multiple lines to start with but it kept throwing back errors. I didn't realise a _ had to be put at the end of each line.
Thats great, thanks very much for your help...


No problem, yell if it doesn't play :)

Hi Chris,
Just another question...

the script i attached earlier also moves files from one dir to another. At the moment it overwrites any existing files..
How would i get it to append instead of overwriting?


Not so easy, it depends on the content of the files. Do we have to drop a header row if it's appending?

No, none of the files have headings. The heading row is added by the software we are using to generate the reports we require.
So all it has to do is append to the existing file (if present)
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks that worked great :)