Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB Script help

Posted on 2011-02-14
15
Medium Priority
?
91 Views
Last Modified: 2014-06-27
Hi, i have the following script which isn't working. Can someone please help me in finding the problem...

 My Script
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?
Thanks
0
Comment
Question by:Chrisneilhopkins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
15 Comments
 
LVL 71

Expert Comment

by:Chris Dent
ID: 34887104

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

Chris
0
 

Author Comment

by:Chrisneilhopkins
ID: 34887125
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.

Thanks
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 34887137

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

Chris
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Chrisneilhopkins
ID: 34887159
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
Next

' 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
	Next
Next

MsgBox "Done" 

Open in new window

test1.txt
0
 

Author Comment

by:Chrisneilhopkins
ID: 34887164
Hmm, seems to have pasted ok actually. :)
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 34887198
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 :)

Chris
0
 

Author Comment

by:Chrisneilhopkins
ID: 34887209
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...

:)
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 34887238

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

Chris
0
 

Author Comment

by:Chrisneilhopkins
ID: 34888212
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?

Thanks
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 34888524

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

Chris
0
 

Author Comment

by:Chrisneilhopkins
ID: 34888829
Chris,
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)
Thanks
0
 
LVL 71

Accepted Solution

by:
Chris Dent earned 2000 total points
ID: 34888957

In theory, like this. Please make sure you test this first, I haven't.

Chris
' FSO Constants

Const FOR_READING = 1
Const FOR_WRITING = 2
Const FOR_APPENDING = 8

' 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", "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")

' 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
Next

' 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) Then 

        ' Needs testing. Merge contents of source file with the existing strNewName file

        Set objSourceFile = objFSO.OpenTextFile(objSubFolder.Path & "\" & strFileName, FOR_READING)
        Set objDestinationFile = objFSO.OpenTextFile(strNewName, FOR_APPENDING)

        Do While objSourceFile.AtEndOfStream <> True
          objDestinationFile.WriteLine objSourceFile.ReadLine
        Loop

        Set objSourceFile = Nothing
        Set objDestinationFile = Nothing

        ' Delete the source file (would have been moved otherwise)
        objFSO.DeleteFile objSubFolder.Path & "\" & strFileName

      Else

        objFSO.MoveFile objSubFolder.Path & "\" & strFileName, strNewName

      End If
    End If

  Next

Next

MsgBox "Done"

Open in new window

0
 

Author Comment

by:Chrisneilhopkins
ID: 34914824
Thanks that worked great :)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question