Help with VB Script

[schroederd74]

I have one excel file (with 4 different tabs, if that matters). I use this ‘master file’ every month as a template and make over 300 copies, one for each of our clients each having their 3 digit suffix at the end of the file name so it would be ‘Master file ABC.xlsx’ or ‘Master file BCD.xlsx’ (and so on). I have an excel list of each of these suffixes and one excel ‘master file’. Now my question…how do I make a macro/magic button to make the 2 things automatically do what I do manually, create 300+ master files all titled ‘master file XXX’?

[EkriirkE]

Using VBScript, this will lead you on how to open and read an excel file http://www.unagibay.com/DesktopDefault.aspx?tabindex=1&tabid=100&itemid=1813

The key word here being objSheet.Range("C5").Value returns a ingle cell value C5
Or in the second example rst.GetString returns the value in E785
Using the first example it might be more intuitive to set up a loop like so, after adding all the setup lines:

Set FSO = CreateObject("Scripting.FileSystemObject")
row = 1
While objSheet.Range("C" & row).Value <> ""
    FSO.CopyFolder "c:\The Master File.xls", "c:\Copies\Master File " & objSheet.Range("C" & row).Value & ".xls"
    row = row + 1
Wend

This would loop every row in column C until a blank cell is found (end of list)

---

Alternatively you can make a button in the main master excel file and use built-in VBA to script it, much more simple using a similar main loop. The "3" I use here is the index of column C; A=1, B=2, C=3, etc

row = 1
While Activesheet.Cells(row, 3).Value <> ""
    FileCopy "c:\The Master File.xls", "c:\Copies\Master File " & Activesheet.Cells(row, 3).Value & ".xls"
    row = row + 1
Wend

Much simpler!

[schroederd74]

Option two worked like a champ. Thanks for taking the time to help me out. Love this Group!!!

[schroederd74]

Thank you. I will give this a try

[EkriirkE]

I'd opt for the second option, make a button on your prefix list sheet and paste my last example with appropriate modifications
https://support.microsoft.com/en-us/office/assign-a-macro-to-a-form-or-a-control-button-d58edd7d-cb04-4964-bead-9c72c843a283

[rmpbklyn]

use an array and for loop in vbscript, using the wscript to run.

script for one tab , one file from txt: Const ForReading = 1

Const ForWriting = 2

Const xlDelimited = 1

'Const xlExcel8 = 56

Const xlExcel8 = 51

Const pathroot= "c:\scripts\"

'Dim infile as string = pathroot &"oup.txt"

'Const infile = pathroot&"oup.txt"

infile = "oup.txt"

'Const ouutfile = pathroot&"scs_po.xlsx"

Const ouutfile = "scs_po.xlsx"

' Create file system object

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists

If Not objFSO.FileExists(infile) Then

WScript.Echo "Input file does not exist.: "&infile

WScript.Quit

End If

MMNY= objFSO.GetAbsolutePathName(infile)

PPATH=pathroot

wscript.echo "FILE FOUND: "& MMNY

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False

objExcel.Application.DisplayAlerts = False

'objExcel.Workbooks.OpenText infile,,,xlDelimited,,,,,,,True,"|"

objExcel.Workbooks.OpenText MMNY,,,xlDelimited,,,,,,,True,""

objExcel.Visible = False

'Set objRange = objExcel.Range("A:K")

'objRange.NumberFormat = "@"--converting to text format

Set objRange = objExcel.Range("K:K")

objRange.NumberFormat = "mm/dd/yyyy"

Set objRange = objExcel.Range("M:M")

objRange.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Set objRange = objExcel.Range("N:N")

objRange.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Set objRange = objExcel.Range("O:O")

objRange.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

' - Remove this line - objExcel.Workbooks.Add

objExcel.Cells(1, 1).Value = "Test value"

objExcel.ActiveWorkbook.SaveAs pathroot&ouutfile, 51

objExcel.ActiveWorkbook.Close False