Script to Import CSV into Excel spreadsheet

Wow, that was fast.  I did not expect a reply until Monday.  I modified the script to eliminate the dialog box so it could run in a noninteractive mode, but the script works great and solved my problem.

However,  if it is not too much trouble, would you walk me through the logic?  I follow some of it from what I could gleam from the Scripting Guys column.  I am most interested in the constants.  (I'm not sure how they define the beginning and end of the spreadsheets.)  I am also very intertested in the line:

objCSVWB.Sheets(1).UsedRange.Copy objTemplateWB.Sheets(1).Cells(65536, "A").End(xlUp).Offset(1, 0)

What is 65536?

If you could also point me in the right direction for good VBScript educational resources, I would also appreciate it.

Best Regards and Happy Holidays
Burzin

Thanks for your help.  When you get a chance see if you can answer the follow up questions.  

Happy Holidays to you too!  Sure, I should start putting more comments in my code.....but I'll explain the Excel parts for you.  Excel is tricky to work with directly from VBScript, as opposed to inside a VBA macro within Excel.  The main reason is because in VBScript, you need to define an Excel specific constant, because VBScript is not aware of those constants.  Their values can be obtained by creating a macro in Excel, and running something like:
   Sub DoStuff
      MsgBox xlUp
   End Sub

Alternatively, there are a few websites that list the Excel constants for you to use.  Also, when using other Excel specific functions (like open a file for example), you cannot use the value constants like FileName:= or UpdateLinks:= because VBScript does not know what those mean, and it tries to interpret those before getting to Excel.
If you have a look here:
http://msdn2.microsoft.com/en-us/library/Aa195811(office.11).aspx
in the Open Method As it Applies to the WorkBooks Object, you see that the syntax is:
expression.Open(FileName, UpdateLinks, ReadOnly......etc, etc

Therefore, as long as you stick to that exact paramater (leaving blank spaces between commas if required for optional parameters) you can pass the required values.

So, just use:
XlApp.Workbooks.Open chr(34) & "M:\Bloomberg\NBF LDN\NBF-BBG-DOWNLOADS" & chr(34), False, False
To open the FileName, set UpdateLinks to False, and ReadOnly to False.

If you have a situation where an Excel macro gives you an Excel constant like "xlAutoOpen" or something, you need to MsgBox xlAutoOpen within an Excel module, so that you know the numeric value of that.  Then, in VBScript, add a constant (assume xlAutoOpen returned 1):
Const xlAutoOpen = 1

and then you can use that constant throughout the code, like
ActiveWorkbook.RunAutoMacros xlAutoOpen

So basically, with those Excel constants that "define the beginning and end of the spreadsheets", they are just Excel values that I have obtained from Excel, and made VBScript "aware" of them.

Now I'll try to break down this for you:
objCSVWB.Sheets(1).UsedRange.Copy objTemplateWB.Sheets(1).Cells(65536, "A").End(xlUp).Offset(1, 0)

The objCSVWB object is a reference to the CSV file workbook.  Then it gets Sheet 1, and references the "Used Range" of the spreadsheet, which is usually the entire data of the spreadsheet (provided that it starts at row 1, column A).  Then the Copy method is invoked.  With the copy method, if you use a parameter, as I have here (the whole part after the word Copy), that acts as the destination for the implicit "Paste" command.  Now this part, which is the destination:
   objTemplateWB.Sheets(1).Cells(65536, "A").End(xlUp).Offset(1, 0)

means that it goes to the Template workbook, sheet 1, and *would* be cell "A65536" (the last cell of a column in Excel 2003), but we've used .End(xlUp), which is the same as placing your cursor in A65536, then pressing CTRL + Up arrow.  This takes you to the last used cell in the column.  Then, lastly, we actually want to write to the next row, so we offset by one row and zero columns.

If there's anything else I can tell you, let me know.

Regards,

Rob.

Oh, and the Microsoft Scripting Articles is a great resource for starting out.  This one's also good:
http://www.computerperformance.co.uk/

Also, use VBSEdit from www.vbsedit.com, as it's fantastic with syntax helping, and property and method auto display.

And basically, just break things down when you want to do something.  Think of one small part to build, google it, and see what you can find.  Also, EE is also a great resource, there's tonnes of scripts here.

Regards,

Rob.