ddalexander1
New Member
- Joined
- Dec 20, 2009
- Messages
- 8
As a novice, I spent many days and searching to accomplish automating this process to run on a remote computer Monday through Friday using the Windows Task Scheduler and I want to share. The process involved the tasks below:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
1.Updating an Excel 2007 workbook with data from an internet site using a Web Query (Menu tabs “Data” and “From Web). When inserting this Web Query Link, choose
<o></o>
2.Generating multiple files from this workbook using arrays of the multiple sheets and saving them to specific directories
<o></o>
3.Loading one of the generated files (.csv) to an external FTP site
<o></o>
4.Loading one of the generated files to an internal Sharepoint Document Library (SPDL)
with two required "Document Properties - Server" (MetaProperties, ContentTypeProperties)
<o></o>
5.Deleting the old file(s) from the SharePoint Document Library (SPDL)
<o></o>
6.The directory and file names contained references to dates in various formats and I was asked to automate the manual process without changes.
<o></o>
I found these preliminary steps necessary in accomplishing this task (remember to do them on the remote computer also if using):
<o></o>
1.Ensure that you have security access and administrative privileges to the computers, directories and Sharepoint sites with which you will be working
<o></o>
2.Add all the directories involved as “Trusted Locations” by going to the Office Button in the upper left-hand corner of the Excel screen, selecting “Excel Options” at the bottom of the menu that opens, then selecting “Trust Center” from the menu on the left, Go to the bottom to the far right of the menu and select “Trust Center Settings. . . “and in the new menu to the left select “Trusted Locations”. If you find any of the directories you will be using missing, Choose to “Add New Locations” at the bottom being careful to select to include Subfolder option in the popup menu after selecting the Directory to be added.
<o></o>
3.Add the SharePoint Document Library (SPDL) URL to your “My Network Places” under “My Computer”. I found this format for the URL being added to work for me - “Https://Server.Company.com/depts./finance/Our Stock Price/”. I also found that after adding the Network Place I had to reboot in order for the macro to find the location.
<o></o>
4.Download the DsoFile.dll from Microsoft website - http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8422 - (choose to Run it to extract the driver (.dll file) being careful to note where the file is saved. Now add it to the Developer Reference Library by going to the Developer Tab of the Excel menu, selecting “Visual Basic, next selecting “Tools”, then selecting “References” from the menu, then “Browse” to the DsoFile.dll, make sure its box is checked and click OK to add it. You may need to add other Libraries. . . but I’m no expert here J
<o></o>
A few things learned:
<o></o>
1.When adding the Web Query link to the workbook for the data (stock prices) which had to be refreshed/updated daily, I used the Excel Menu tabs “Data” and “From Web”, I unchecked the box “Enable background refresh” and checked the boxes “Refresh data when opening the file” and “Overwrite existing cells with new data, clear unused cells”. Then I selected and named a range that was sufficient to cover all of the data being pulled in from the website (I had to expand this range down a bit to accommodate additions that might be made to the dataset on the website over time). This way I was able to reference the data elsewhere in the workbook using a vlookup.
<o></o>
2.I was not able to run a script or batch file using the Task Scheduler on the remote computer while “logged off” but was able to Open the Excel Workbook using the Task Scheduler while “logged off”. Therefore, the process needed to be automated from a macro within the workbook.
<o></o>
3.To successfully schedule the task to open the workbook while I was “logged off” the remote computer I found I needed to create a task on the remote computer that opened the Internet Explorer (3 minutes before) as a preliminary step to a second task that opened the Workbook.
<o></o>
4.I was able to create a “BeforeOpen” event macro in the workbook that ran the Web Query which updated the data within the Workbook. This “BeforeOpen” macro then called the Macro “Save_Daily_Files” which completed the rest of the process.
<o></o>
5.To load the csv file to the External FTP site, I had to create a batch file to do the job and then call the batch file from a macro in the workbook.
<o></o>
6.I found that Excel 2007 Workbooks, whether .xlsx or .xlsm could only be uploaded to the Sharepoint list (Document Library) (SPDL) as Read-Only files. I also believe that to complete this process the .xlsm file format should be used for the load to the SPDL
<o></o>
7.When I tried to load an Excel 97-2003 .xls file from the macro to the SharePoint Document Library (SPDL) I received an error message “Disk is Full” which I knew was not the case. I did learn that when loading a file to an SPDL the space required is double that of the file because the upload first loads a copy and then saves the file before deleting the copy.
<o></o>
8. From the macro I was unable to add the required "ContentTypeProperties" to the Exel 2007 .xlsm file before loading and was unable to load the file to the SPDL using the Save function. BUT, I could load it using the SaveCopyAs function. (I added an underscore (_) to the end of the file name for the SaveCopyAs Function so I could remove it in the next step leaving me with the correct file name.) Once this Read-Only file (without required "ContentTypeProperties") was loaded, I could Open it, update the "ContentTypeProperties", and, then again, SaveCopyAs to load the updated file back to the SPDL without the underscore in the name. This left me with two files to be deleted from the SPDL, yesterday's old file and the first file loaded without the "ContentTypeProperties.
<o></o>
9.The “My Network Places” is a short-cut and not a true directory to the SPDL which does have some ramifications.
<o></o>
10. To load the .csv file to the external FTP site I had to create a separate vbscript file and a separate batch file (code for both is below). Then I called the batch file from within the macro and the batch file runs the vbscript file before returning control to the macro within the workbook.
<o></o>
11.I needed the Auto_Close macro to Quit the Excel Application that I had opened with the Windows Task Scheduler and save the Workbook that would be opened again as scheduled the next day. Otherwise, I would close the workbook, yet leave Excel open on the remote computer.
<o></o>
The code is included here, but I reiterate that I am a novice and not a trained programmer. I have stolen bits and pieces from others who have taken the time to document their work on the web and this is my first contribution to that valuable resource. I admit to plagiarism and express my gratitude to those I have plagiarized who find copies of their code below. This is not what I would refer to as good code as the proper error handling is missing and who knows what other errors I have committed J
<o></o>
Open Event Code here:
<o></o>
<o></o>
<o></o>
<o></o>
Macros to be added to a Module within the Workbook are here:
<o></o>
<o></o>
<o></o>
<o></o>
The Script File for upload to FTP site is here. Just add the code to a text file in Notepad and save with the .vbs name extension instead of the .txt extension:
<o></o>
<o></o>
<o></o>
<o></o>
And the Batch File called to run the Script File for the FTP Upload is here. Just add the code to a text file in Notepad and save with the .bat file name extension instead of the .txt extension:
<o></o>
<o></o>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>1.Updating an Excel 2007 workbook with data from an internet site using a Web Query (Menu tabs “Data” and “From Web). When inserting this Web Query Link, choose
<o
></o>2.Generating multiple files from this workbook using arrays of the multiple sheets and saving them to specific directories
<o
></o>3.Loading one of the generated files (.csv) to an external FTP site
<o
></o>4.Loading one of the generated files to an internal Sharepoint Document Library (SPDL)
with two required "Document Properties - Server" (MetaProperties, ContentTypeProperties)
<o
></o>5.Deleting the old file(s) from the SharePoint Document Library (SPDL)
<o
></o>6.The directory and file names contained references to dates in various formats and I was asked to automate the manual process without changes.
<o
></o>I found these preliminary steps necessary in accomplishing this task (remember to do them on the remote computer also if using):
<o
></o>1.Ensure that you have security access and administrative privileges to the computers, directories and Sharepoint sites with which you will be working
<o
></o>2.Add all the directories involved as “Trusted Locations” by going to the Office Button in the upper left-hand corner of the Excel screen, selecting “Excel Options” at the bottom of the menu that opens, then selecting “Trust Center” from the menu on the left, Go to the bottom to the far right of the menu and select “Trust Center Settings. . . “and in the new menu to the left select “Trusted Locations”. If you find any of the directories you will be using missing, Choose to “Add New Locations” at the bottom being careful to select to include Subfolder option in the popup menu after selecting the Directory to be added.
<o
></o>3.Add the SharePoint Document Library (SPDL) URL to your “My Network Places” under “My Computer”. I found this format for the URL being added to work for me - “Https://Server.Company.com/depts./finance/Our Stock Price/”. I also found that after adding the Network Place I had to reboot in order for the macro to find the location.
<o
></o>4.Download the DsoFile.dll from Microsoft website - http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8422 - (choose to Run it to extract the driver (.dll file) being careful to note where the file is saved. Now add it to the Developer Reference Library by going to the Developer Tab of the Excel menu, selecting “Visual Basic, next selecting “Tools”, then selecting “References” from the menu, then “Browse” to the DsoFile.dll, make sure its box is checked and click OK to add it. You may need to add other Libraries. . . but I’m no expert here J
<o
></o>A few things learned:
<o
></o>1.When adding the Web Query link to the workbook for the data (stock prices) which had to be refreshed/updated daily, I used the Excel Menu tabs “Data” and “From Web”, I unchecked the box “Enable background refresh” and checked the boxes “Refresh data when opening the file” and “Overwrite existing cells with new data, clear unused cells”. Then I selected and named a range that was sufficient to cover all of the data being pulled in from the website (I had to expand this range down a bit to accommodate additions that might be made to the dataset on the website over time). This way I was able to reference the data elsewhere in the workbook using a vlookup.
<o
></o>2.I was not able to run a script or batch file using the Task Scheduler on the remote computer while “logged off” but was able to Open the Excel Workbook using the Task Scheduler while “logged off”. Therefore, the process needed to be automated from a macro within the workbook.
<o
></o>3.To successfully schedule the task to open the workbook while I was “logged off” the remote computer I found I needed to create a task on the remote computer that opened the Internet Explorer (3 minutes before) as a preliminary step to a second task that opened the Workbook.
<o
></o>4.I was able to create a “BeforeOpen” event macro in the workbook that ran the Web Query which updated the data within the Workbook. This “BeforeOpen” macro then called the Macro “Save_Daily_Files” which completed the rest of the process.
<o
></o>5.To load the csv file to the External FTP site, I had to create a batch file to do the job and then call the batch file from a macro in the workbook.
<o
></o>6.I found that Excel 2007 Workbooks, whether .xlsx or .xlsm could only be uploaded to the Sharepoint list (Document Library) (SPDL) as Read-Only files. I also believe that to complete this process the .xlsm file format should be used for the load to the SPDL
<o
></o>7.When I tried to load an Excel 97-2003 .xls file from the macro to the SharePoint Document Library (SPDL) I received an error message “Disk is Full” which I knew was not the case. I did learn that when loading a file to an SPDL the space required is double that of the file because the upload first loads a copy and then saves the file before deleting the copy.
<o
></o>8. From the macro I was unable to add the required "ContentTypeProperties" to the Exel 2007 .xlsm file before loading and was unable to load the file to the SPDL using the Save function. BUT, I could load it using the SaveCopyAs function. (I added an underscore (_) to the end of the file name for the SaveCopyAs Function so I could remove it in the next step leaving me with the correct file name.) Once this Read-Only file (without required "ContentTypeProperties") was loaded, I could Open it, update the "ContentTypeProperties", and, then again, SaveCopyAs to load the updated file back to the SPDL without the underscore in the name. This left me with two files to be deleted from the SPDL, yesterday's old file and the first file loaded without the "ContentTypeProperties.
<o
></o>9.The “My Network Places” is a short-cut and not a true directory to the SPDL which does have some ramifications.
<o
></o>10. To load the .csv file to the external FTP site I had to create a separate vbscript file and a separate batch file (code for both is below). Then I called the batch file from within the macro and the batch file runs the vbscript file before returning control to the macro within the workbook.
<o
></o>11.I needed the Auto_Close macro to Quit the Excel Application that I had opened with the Windows Task Scheduler and save the Workbook that would be opened again as scheduled the next day. Otherwise, I would close the workbook, yet leave Excel open on the remote computer.
<o
></o>The code is included here, but I reiterate that I am a novice and not a trained programmer. I have stolen bits and pieces from others who have taken the time to document their work on the web and this is my first contribution to that valuable resource. I admit to plagiarism and express my gratitude to those I have plagiarized who find copies of their code below. This is not what I would refer to as good code as the proper error handling is missing and who knows what other errors I have committed J
<o
></o>Open Event Code here:
<o
></o><o
></o>
Code:
[FONT=Arial][COLOR=black]Private Sub Workbook_Open()[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]‘This activates the sheet on which the Web Query has been set.[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Application.Sheets("DailyStockPrice").Activate[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]‘Gives the WebQuery time to run[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Application.Wait (Now + TimeValue("0:00:15"))[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]Application.ActiveWorkbook.RefreshAll[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]Call Save_Daily_Files ' Call your macro[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]‘Actions below proceed after the macro ‘Save_Daily_Files’ completes its run[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]ActiveWorkbook.CheckCompatibility = False[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]ActiveWorkbook.Close[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]End Sub[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]<o
></o>Macros to be added to a Module within the Workbook are here:
<o
></o><o
></o>
Code:
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial]Option Explicit[/FONT]
[FONT=Arial]‘++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++[/FONT]
[FONT=Arial]Sub Save_Daily_Files()[/FONT]
[FONT=Arial]Dim WB As Workbook[/FONT]
[FONT=Arial]Dim NB As Workbook[/FONT]
[FONT=Arial]Dim NB2 As Workbook[/FONT]
[FONT=Arial]Dim NB3 As Workbook[/FONT]
[FONT=Arial]Dim NB4 As Workbook[/FONT]
[FONT=Arial]Dim S As Worksheet[/FONT]
[FONT=Arial]Dim FindString As Date[/FONT]
[FONT=Arial]Dim FindString2 As Date[/FONT]
[FONT=Arial]Dim Rng As Range[/FONT]
[FONT=Arial]Dim Rng2 As Range[/FONT]
[FONT=Arial]Dim Rng3 As Range[/FONT]
[FONT=Arial]Dim ThisBook As String[/FONT]
[FONT=Arial]Dim LWeekday As Integer[/FONT]
[FONT=Arial]Dim ThisYr As String[/FONT]
[FONT=Arial]Dim MyPath As String[/FONT]
[FONT=Arial]Dim SaveFormat As Long[/FONT]
[FONT=Arial]Dim FtpFileUpload As String[/FONT]
[FONT=Arial]Dim WshNetwork[/FONT]
<o:p></o:p>
[FONT=Arial]With Application[/FONT]
[FONT=Arial].ScreenUpdating = False[/FONT]
[FONT=Arial].EnableEvents = False[/FONT]
[FONT=Arial].DisplayAlerts = False[/FONT]
[FONT=Arial].Calculation = xlCalculationAutomatic[/FONT]
[FONT=Arial]End With[/FONT]
<o:p></o:p>
[FONT=Arial]Set WB = ActiveWorkbook[/FONT]
[FONT=Arial]MyPath = WB.Path[/FONT]
[FONT=Arial]ThisBook = WB.Name[/FONT]
[FONT=Arial]LWeekday = Weekday((Date), vbSunday) 'Determines Day of Week for today[/FONT]
[FONT=Arial]ThisYr = Format(Date + 1, "yyyy")[/FONT]
[FONT=Arial]FtpFileUpload = "C:\Windows\Scripts\FtpFileUpload.bat"[/FONT]
[FONT=Arial]'Determines if Day of Week is Friday (6) and then sets stock values for Saturday and Sunday[/FONT]
[FONT=Arial]If LWeekday = 6 Then[/FONT]
[FONT=Arial]FindString = CLng(Date) + 3[/FONT]
[FONT=Arial]FindString2 = CLng(DateSerial(ThisYr, 1, 1))[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial]FindString = CLng(Date) + 1[/FONT]
[FONT=Arial]FindString2 = CLng(DateSerial(ThisYr, 1, 1))[/FONT]
[FONT=Arial]End If[/FONT]
<o:p></o:p>
[FONT=Arial]Application.ActiveWorkbook.Save[/FONT]
<o:p></o:p>
[FONT=Arial]'*********************************************************************************[/FONT]
[FONT=Arial]'Creates the Daily file[/FONT]
<o:p></o:p>
[FONT=Arial]Application.Sheets(Array(1, "2010 Historical Stock Prices", "2010 LTIP Daily Calculation", _[/FONT]
[FONT=Arial] "2000,2006 Our Stock Price", "2011 Historical Stock Prices", _[/FONT]
[FONT=Arial] "2011 LTIP Daily Calculation")).Copy[/FONT]
[FONT=Arial]Set NB = ActiveWorkbook[/FONT]
[FONT=Arial]NB.CheckCompatibility = False[/FONT]
[FONT=Arial]NB.SaveAs FileName:="T:\Benefits\Executive Benefits\Our Stock\" & _[/FONT]
[FONT=Arial] ThisYr & " Daily Unit Val Calc\" & Format(Date + 1, "m-d-yyyy") & ".xlsm", _[/FONT]
[FONT=Arial] FileFormat:=52, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False[/FONT]
<o:p></o:p>
[FONT=Arial]Set NB = ActiveWorkbook[/FONT]
<o:p></o:p>
[FONT=Arial]'**************************************************************************[/FONT]
[FONT=Arial]With NB[/FONT]
[FONT=Arial].Sheets(1).Cells.Copy[/FONT]
[FONT=Arial].Sheets(1).Cells.PasteSpecial (xlPasteValues)[/FONT]
[FONT=Arial]Application.CutCopyMode = False[/FONT]
[FONT=Arial].Sheets("2010 Historical Stock Prices").Cells.Copy[/FONT]
[FONT=Arial].Sheets("2010 Historical Stock Prices").Cells.PasteSpecial (xlPasteValues)[/FONT]
[FONT=Arial]Application.CutCopyMode = False[/FONT]
<o:p></o:p>
[FONT=Arial].Sheets("2010 LTIP Daily Calculation").Cells.Copy[/FONT]
[FONT=Arial].Sheets("2010 LTIP Daily Calculation").Cells.PasteSpecial (xlPasteValues)[/FONT]
[FONT=Arial]Application.CutCopyMode = False[/FONT]
[FONT=Arial]Application.Goto Sheets("2010 LTIP Daily Calculation").Cells(2, 1)[/FONT]
<o:p></o:p>
[FONT=Arial].Sheets("2011 Historical Stock Prices").Cells.Copy[/FONT]
[FONT=Arial].Sheets("2011 Historical Stock Prices").Cells.PasteSpecial (xlPasteValues)[/FONT]
[FONT=Arial]Application.CutCopyMode = False[/FONT]
[FONT=Arial].Sheets("2011 LTIP Daily Calculation").Cells.Copy[/FONT]
[FONT=Arial].Sheets("2011 LTIP Daily Calculation").Cells.PasteSpecial (xlPasteValues)[/FONT]
[FONT=Arial]Application.CutCopyMode = False[/FONT]
[FONT=Arial]Application.Goto Sheets("2011 LTIP Daily Calculation").Cells(2, 1)[/FONT]
<o:p></o:p>
[FONT=Arial]End With[/FONT]
[FONT=Arial]'*******************************************************************[/FONT]
[FONT=Arial]'Deletes all historical rows other than those associated with current year[/FONT]
[FONT=Arial]Application.Sheets("2010 Historical Stock Prices").Activate[/FONT]
[FONT=Arial]With Sheets("2010 Historical Stock Prices").Range("A:A")[/FONT]
[FONT=Arial] Set Rng3 = .Find(What:=FindString2, _[/FONT]
[FONT=Arial] After:=.Cells(.Cells.Count), _[/FONT]
[FONT=Arial] LookIn:=xlValues, _[/FONT]
[FONT=Arial] LookAt:=xlWhole, _[/FONT]
[FONT=Arial] SearchOrder:=xlByRows, _[/FONT]
[FONT=Arial] SearchDirection:=xlNext, _[/FONT]
[FONT=Arial] MatchCase:=False)[/FONT]
[FONT=Arial]End With[/FONT]
[FONT=Arial] If Not Rng3 Is Nothing And ThisYr > Year(Cells(5, 1)) Then[/FONT]
[FONT=Arial] Rng3.Offset(-((Rng3.Row) - 5), 0).Resize(Rng3.Row - 5, 100).Delete[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial] If Not Rng3 Is Nothing Then[/FONT]
[FONT=Arial] ActiveSheet.Cells(371, 1).Resize(10000, 100).Delete[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial] End If[/FONT]
[FONT=Arial] End If[/FONT]
[FONT=Arial]Rng3.Select[/FONT]
[FONT=Arial]Set Rng3 = Nothing[/FONT]
<o:p></o:p>
[FONT=Arial]'*******************************************************************[/FONT]
[FONT=Arial]'Deletes all historical rows other than those associated with current year[/FONT]
[FONT=Arial]Application.Sheets("2011 Historical Stock Prices").Activate[/FONT]
[FONT=Arial]With Sheets("2011 Historical Stock Prices").Range("A:A")[/FONT]
[FONT=Arial] Set Rng2 = .Find(What:=FindString2, _[/FONT]
[FONT=Arial] After:=.Cells(.Cells.Count), _[/FONT]
[FONT=Arial] LookIn:=xlValues, _[/FONT]
[FONT=Arial] LookAt:=xlWhole, _[/FONT]
[FONT=Arial] SearchOrder:=xlByRows, _[/FONT]
[FONT=Arial] SearchDirection:=xlNext, _[/FONT]
[FONT=Arial] MatchCase:=False)[/FONT]
[FONT=Arial]End With[/FONT]
[FONT=Arial] If Not Rng2 Is Nothing And ThisYr > Year(Cells(5, 1)) Then[/FONT]
[FONT=Arial] Rng2.Offset(-((Rng2.Row) - 5), 0).Resize(Rng2.Row - 5, 100).Delete[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial] If Not Rng2 Is Nothing Then[/FONT]
[FONT=Arial] ActiveSheet.Cells(371, 1).Resize(10000, 100).Delete[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial] End If[/FONT]
[FONT=Arial] End If[/FONT]
[FONT=Arial]Rng2.Select[/FONT]
[FONT=Arial]Set Rng2 = Nothing[/FONT]
[FONT=Arial]'***************************************************************************[/FONT]
[FONT=Arial]'Removes all the now-dead Named references in the new workbook[/FONT]
[FONT=Arial]Dim BadRef As String[/FONT]
[FONT=Arial]Dim R As Integer[/FONT]
[FONT=Arial]Dim N As Name[/FONT]
[FONT=Arial]R = 1[/FONT]
[FONT=Arial]For Each N In ActiveWorkbook.Names[/FONT]
[FONT=Arial]BadRef = N.Name[/FONT]
[FONT=Arial]Names(BadRef).Delete[/FONT]
[FONT=Arial]R = R + 1[/FONT]
[FONT=Arial]Next N[/FONT]
[FONT=Arial]'************************************************************[/FONT]
[FONT=Arial]'Creates daily CSV file for upload to ADP Seattle[/FONT]
[FONT=Arial]NB.Worksheets(1).Activate[/FONT]
[FONT=Arial]ActiveSheet.Move[/FONT]
[FONT=Arial]'********************************************************[/FONT]
[FONT=Arial]Set NB3 = ActiveWorkbook[/FONT]
[FONT=Arial]NB3.SaveAs FileName:="T:\Benefits\Executive Benefits\Our Stock\" & _[/FONT]
[FONT=Arial]ThisYr & " Daily Unit Val Calc\Company_" & Format(Date, "mmddyy") & ".csv", _[/FONT]
[FONT=Arial]FileFormat:=xlCSV, CreateBackup:=False[/FONT]
[FONT=Arial]ActiveWorkbook.Close False[/FONT]
[FONT=Arial]Application.DefaultSaveFormat = 52[/FONT]
<o:p></o:p>
[FONT=Arial]'*********************************************************************************[/FONT]
[FONT=Arial]'Creates and saves the Daily file for SERVER Upload[/FONT]
<o:p></o:p>
[FONT=Arial]NB.Sheets(1).Activate[/FONT]
<o:p></o:p>
[FONT=Arial]Application.Sheets(Array("2010 Historical Stock Prices", "2010 LTIP Daily Calculation", _[/FONT]
[FONT=Arial]"2000,2006 Our Stock Price")).Select[/FONT]
[FONT=Arial]Application.Sheets(Array("2010 Historical Stock Prices", "2010 LTIP Daily Calculation", _[/FONT]
[FONT=Arial]"2000,2006 Our Stock Price")).Copy[/FONT]
<o:p></o:p>
[FONT=Arial]Set NB2 = ActiveWorkbook[/FONT]
[FONT=Arial]NB2.Sheets(1).Cells(1, 1).Select[/FONT]
<o:p></o:p>
[FONT=Arial]NB2.CheckCompatibility = False[/FONT]
<o:p></o:p>
[FONT=Arial]Application.ActiveWorkbook.CustomDocumentProperties.Add _[/FONT]
[FONT=Arial]Name:="Classification", _[/FONT]
[FONT=Arial]LinkToContent:=False, _[/FONT]
[FONT=Arial]Type:=msoPropertyTypeString, _[/FONT]
[FONT=Arial]Value:="Report", _[/FONT]
[FONT=Arial]LinkSource:=False[/FONT]
[FONT=Arial]Application.ActiveWorkbook.CustomDocumentProperties.Add _[/FONT]
[FONT=Arial]Name:="Department" & "/" & "Group", _[/FONT]
[FONT=Arial]LinkToContent:=False, _[/FONT]
[FONT=Arial]Type:=msoPropertyTypeString, _[/FONT]
[FONT=Arial]Value:="Human Resources", _[/FONT]
[FONT=Arial]LinkSource:=False[/FONT]
<o:p></o:p>
[FONT=Arial]NB2.BuiltinDocumentProperties("Title") = "Our_Stock_Prices"[/FONT]
<o:p></o:p>
[FONT=Arial]NB2.SaveAs FileName:="T:\Benefits\Executive Benefits\Our Stock\" & _[/FONT]
[FONT=Arial]ThisYr & " Daily Unit Val Calc\" & Format(Date + 1, "m-d-yyyy") & _[/FONT]
[FONT=Arial]"-Stock Prices.xlsm", FileFormat:=52, CreateBackup:=False[/FONT]
[FONT=Arial]NB2.SaveCopyAs FileName:="\\Server.Company.com\depts\finance\Our Stock Price\" & Format(Date + 1, "m-d-yyyy") & "-Stock Prices_1.xlsm"[/FONT]
<o:p></o:p>
[FONT=Arial]Application.Wait (Now + TimeValue("0:00:18"))[/FONT]
<o:p></o:p>
[FONT=Arial]ActiveWorkbook.Close[/FONT]
[FONT=Arial]Call Add_Todays_File[/FONT]
<o:p></o:p>
[FONT=Arial]Call Delete_Old_Excels[/FONT]
<o:p></o:p>
[FONT=Arial]'******************************************************************[/FONT]
<o:p></o:p>
[FONT=Arial]Set NB = ActiveWorkbook[/FONT]
[FONT=Arial]NB.Sheets(1).Select[/FONT]
[FONT=Arial]NB.Save[/FONT]
[FONT=Arial]NB.Close savechanges:=False[/FONT]
<o:p></o:p>
[FONT=Arial]'******************************************************************************[/FONT]
[FONT=Arial]'Creates and opens an e-mail Deneen's file attached.[/FONT]
[FONT=Arial]'Dim olApp As Outlook.Application[/FONT]
[FONT=Arial]'Dim olMail As Outlook.MailItem[/FONT]
[FONT=Arial]'Dim oFolder As Outlook.MAPIFolder[/FONT]
[FONT=Arial]'Dim MoOutlook As Outlook.Namespace[/FONT]
<o:p></o:p>
[FONT=Arial]'Set olApp = CreateObject("Outlook.Application") 'reference to Outlook[/FONT]
[FONT=Arial]'Set MoOutlook = olApp.GetNamespace("MAPI")[/FONT]
[FONT=Arial]'Set oFolder = MoOutlook.GetDefaultFolder(olFolderOutbox)[/FONT]
[FONT=Arial]'Set olMail = olApp.CreateItem(olMailItem) 'reference to new e-mail[/FONT]
<o:p></o:p>
[FONT=Arial]'Dim olApp As Outlook.Application[/FONT]
[FONT=Arial]'Dim olMail As Outlook.MailItem[/FONT]
[FONT=Arial]'Dim oFolder As Outlook.MAPIFolder[/FONT]
[FONT=Arial]'Dim MoOutlook As Outlook.Namespace[/FONT]
[FONT=Arial]'Dim cmMailBoxName As String[/FONT]
<o:p></o:p>
[FONT=Arial]'cmMailBoxName = "CompanyComp@Company.com"[/FONT]
[FONT=Arial]'Set olApp = CreateObject("Outlook.Application") 'reference to Outlook[/FONT]
[FONT=Arial]'Set MoOutlook = olApp.GetNamespace("MAPI")[/FONT]
[FONT=Arial]'Set oFolder = MoOutlook.Folders(cmMailBoxName)[/FONT]
[FONT=Arial]'Set olMail = olApp.CreateItem(olMailItem) 'reference to new e-mail[/FONT]
[FONT=Arial]'With olMail[/FONT]
[FONT=Arial]'.Importance = olImportanceHigh[/FONT]
[FONT=Arial]'.To = "CompanyComp@Company.com"[/FONT]
[FONT=Arial]'.CC = "AKlein@Company.com"[/FONT]
[FONT=Arial]'.BCC = "DDAlexander@Company.com"[/FONT]
[FONT=Arial]'.Subject = "T:\Benefits\Executive Benefits\Our Stock\" & _[/FONT]
[FONT=Arial]'ThisYr & "Daily Unit Val Calc\" & Format(Date + 1, "m-d-yyyy") & _[/FONT]
[FONT=Arial]'" for Deneen.xlsm"""[/FONT]
[FONT=Arial]'.Body = "Abbie Klein" & Chr$(13) & "Company Construction Company" & Chr$(13) & _[/FONT]
[FONT=Arial] '"375 Hudson Street" & Chr$(13) & "6th Floor" & Chr$(13) & _[/FONT]
[FONT=Arial] '"New York, New York 10014" & Chr$(13) & "(Phone) 212-229-6370" & Chr$(13) & _[/FONT]
[FONT=Arial] '"(Fax) 212-229-6024"[/FONT]
[FONT=Arial]'.Attachments.Add "T:\Benefits\Executive Benefits\Our Stock\" & _[/FONT]
[FONT=Arial]'ThisYr & " Daily Unit Val Calc\" & Format(Date + 1, "m-d-yyyy") & _[/FONT]
[FONT=Arial]'" for Deneen.xlsm", olByValue, 1[/FONT]
[FONT=Arial]'.Send 'will send e-mail without editing[/FONT]
<o:p></o:p>
[FONT=Arial]'End With[/FONT]
<o:p></o:p>
[FONT=Arial]'***********************************************************************************[/FONT]
[FONT=Arial]'Converts today's historical values from formula to value[/FONT]
[FONT=Arial]'Activates, Saves, and Closes the Template[/FONT]
<o:p></o:p>
[FONT=Arial]WB.Sheets("2010 Historical Stock Prices").Activate[/FONT]
<o:p></o:p>
[FONT=Arial]With Sheets("2010 Historical Stock Prices").Range("A:A")[/FONT]
[FONT=Arial] Set Rng = .Find(What:=FindString, _[/FONT]
[FONT=Arial] After:=.Cells(.Cells.Count), _[/FONT]
[FONT=Arial] LookIn:=xlValues, _[/FONT]
[FONT=Arial] LookAt:=xlWhole, _[/FONT]
[FONT=Arial] SearchOrder:=xlByRows, _[/FONT]
[FONT=Arial] SearchDirection:=xlNext, _[/FONT]
[FONT=Arial] MatchCase:=False)[/FONT]
[FONT=Arial]End With[/FONT]
[FONT=Arial]If Not Rng Is Nothing Then[/FONT]
[FONT=Arial] Rng.Offset(-((Rng.Row) - 5), 0).Resize(Rng.Row - 4, 5).Copy[/FONT]
[FONT=Arial] Rng.Offset(-((Rng.Row) - 5), 0).Resize(Rng.Row - 4, 5).PasteSpecial xlPasteValues[/FONT]
[FONT=Arial] Application.CutCopyMode = False[/FONT]
[FONT=Arial] Application.Goto Cells(1, 1)[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial] MsgBox "Current Date not found in 2010_Historical_Stock_Prices sheet."[/FONT]
[FONT=Arial]End If[/FONT]
<o:p></o:p>
[FONT=Arial]Set Rng = Nothing[/FONT]
[FONT=Arial]'*******************************************************************[/FONT]
[FONT=Arial]Application.Sheets("2011 Historical Stock Prices").Activate[/FONT]
<o:p></o:p>
[FONT=Arial]With Sheets("2011 Historical Stock Prices").Range("A:A")[/FONT]
[FONT=Arial] Set Rng = .Find(What:=FindString, _[/FONT]
[FONT=Arial] After:=.Cells(.Cells.Count), _[/FONT]
[FONT=Arial] LookIn:=xlValues, _[/FONT]
[FONT=Arial] LookAt:=xlWhole, _[/FONT]
[FONT=Arial] SearchOrder:=xlByRows, _[/FONT]
[FONT=Arial] SearchDirection:=xlNext, _[/FONT]
[FONT=Arial] MatchCase:=False)[/FONT]
[FONT=Arial]End With[/FONT]
[FONT=Arial]If Not Rng Is Nothing Then[/FONT]
[FONT=Arial] Rng.Offset(-((Rng.Row) - 5), 0).Resize(Rng.Row - 4, 5).Copy[/FONT]
[FONT=Arial] Rng.Offset(-((Rng.Row) - 5), 0).Resize(Rng.Row - 4, 5).PasteSpecial xlPasteValues[/FONT]
[FONT=Arial] Application.CutCopyMode = False[/FONT]
[FONT=Arial] Application.Goto Cells(1, 1)[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial] MsgBox "Current Date not found in 2011_Historical_Stock_Prices sheet."[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]Set Rng = Nothing[/FONT]
[FONT=Arial]'***********************************************************[/FONT]
[FONT=Arial]Application.Sheets(2).Activate[/FONT]
[FONT=Arial]Cells(2, 1).Select[/FONT]
[FONT=Arial]WB.Save[/FONT]
[FONT=Arial]Application.Wait (Now + TimeValue("0:00:10"))[/FONT]
<o:p></o:p>
[FONT=Arial]FtpFileUpload = "C:\Windows\Scripts\FtpFileUpload.bat"[/FONT]
[FONT=Arial]Shell (FtpFileUpload)[/FONT]
<o:p></o:p>
[FONT=Arial]Application.Wait (Now + TimeValue("0:00:15"))[/FONT]
[FONT=Arial]Set WB = Nothing[/FONT]
[FONT=Arial]With Application[/FONT]
[FONT=Arial] .ScreenUpdating = True[/FONT]
[FONT=Arial] .EnableEvents = True[/FONT]
[FONT=Arial] .DisplayAlerts = True[/FONT]
[FONT=Arial] .CalculateBeforeSave = True[/FONT]
[FONT=Arial] .DefaultSaveFormat = SaveFormat[/FONT]
[FONT=Arial]End With[/FONT]
<o:p></o:p>
[FONT=Arial]End Sub[/FONT]
[FONT=Arial]‘+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++[/FONT]
<o:p></o:p>
[FONT=Arial]Sub Auto_Close()[/FONT]
[FONT=Arial]Application.Quit ' Quit Excel[/FONT]
[FONT=Arial]If ThisWorkbook.Saved = False Then[/FONT]
[FONT=Arial] ThisWorkbook.Save[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]End Sub[/FONT]
[FONT=Arial]‘+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++[/FONT]
<o:p></o:p>
[FONT=Arial]Sub Add_Todays_File()[/FONT]
<o:p></o:p>
[FONT=Arial]Dim NB4 As Workbook[/FONT]
[FONT=Arial]Dim xlFile As String[/FONT]
[FONT=Arial]Dim metaprop As MetaProperty[/FONT]
<o:p></o:p>
[FONT=Arial]xlFile = "\\Server.Company.com\depts\finance\Our Stock Price\" & Format(Date + 1, "m-d-yyyy") & _[/FONT]
[FONT=Arial]"-Stock Prices_1.xlsm"[/FONT]
<o:p></o:p>
[FONT=Arial]Set NB4 = Nothing[/FONT]
[FONT=Arial]Set NB4 = Application.Workbooks.Open(xlFile, , False)[/FONT]
<o:p></o:p>
[FONT=Arial]Application.Wait (Now + TimeValue("0:00:15"))[/FONT]
<o:p></o:p>
[FONT=Arial]On Error Resume Next[/FONT]
[FONT=Arial]For Each metaprop In ActiveWorkbook.ContentTypeProperties[/FONT]
[FONT=Arial]If metaprop.Name = "Classification" Then[/FONT]
[FONT=Arial]metaprop.Value = "Report"[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]If metaprop.Name = "Department" & "/" & "Group" Then[/FONT]
[FONT=Arial]metaprop.Value = "Human Resources"[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]Next[/FONT]
[FONT=Arial]On Error GoTo 0[/FONT]
<o:p></o:p>
[FONT=Arial]Application.Wait (Now + TimeValue("0:00:15"))[/FONT]
<o:p></o:p>
[FONT=Arial]NB4.SaveCopyAs FileName:="\\Server.Company.com\depts\finance\Our Stock Price\" & Format(Date + 1, "m-d-yyyy") & _[/FONT]
[FONT=Arial]"-Stock Prices.xlsm"[/FONT]
<o:p></o:p>
[FONT=Arial]Application.Wait (Now + TimeValue("0:00:18"))[/FONT]
[FONT=Arial]NB4.Close[/FONT]
[FONT=Arial]Set NB4 = Nothing[/FONT]
[FONT=Arial]End Sub[/FONT]
[FONT=Arial]‘+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++[/FONT]
<o:p></o:p>
[FONT=Arial]Sub Delete_Old_Excels()[/FONT]
<o:p></o:p>
[FONT=Arial]Dim objFSO As Object[/FONT]
[FONT=Arial]Dim objFolder As Object[/FONT]
[FONT=Arial]Dim ObjFile As Object[/FONT]
[FONT=Arial]Dim pth As String[/FONT]
[FONT=Arial]Dim FileNme As String[/FONT]
[FONT=Arial]Dim Nm1 As Integer[/FONT]
[FONT=Arial]Dim Nm2 As Integer[/FONT]
[FONT=Arial]Set objFSO = CreateObject("Scripting.FileSystemObject")[/FONT]
<o:p></o:p>
[FONT=Arial]'Get the folder object associated with the directory[/FONT]
[FONT=Arial]Set objFolder = objFSO.GetFolder("\\Server.Company.com\depts\finance\Our Stock Price\")[/FONT]
<o:p></o:p>
[FONT=Arial]'** Youll need to specify your path here. By removing the http: from the path, the code liked it & found the folder. It wasn’t working previously ***[/FONT]
[FONT=Arial]pth = "https://Server.Company.com/depts/finance/Our Stock Price/"[/FONT]
<o:p></o:p>
[FONT=Arial]'** Youll need to specify your path here. The reason I’ve done this separately is because the path is not recognised otherwise when trying to specify it with workbook.open & using the value set for objFolder **[/FONT]
<o:p></o:p>
[FONT=Arial]'Loop through the Files collection[/FONT]
[FONT=Arial]For Each ObjFile In objFolder.Files[/FONT]
[FONT=Arial] Nm1 = Len("\\Server.Company.com\depts\finance\Our Stock Price\")[/FONT]
[FONT=Arial] '** Youll need to specify your path here **[/FONT]
[FONT=Arial] Nm2 = Len(ObjFile) - Nm1[/FONT]
[FONT=Arial] FileNme = Right(ObjFile, Nm2)[/FONT]
[FONT=Arial] '** I’ve done this part to find out/set the file name**[/FONT]
[FONT=Arial] If FileNme = Format(Date, "m-d-yyyy") & "-Stock Prices.xlsm" Then[/FONT]
[FONT=Arial] ObjFile.Delete[/FONT]
[FONT=Arial] ElseIf FileNme = Format(Date - 2, "m-d-yyyy") & "-Stock Prices.xlsm" Then[/FONT]
[FONT=Arial] ObjFile.Delete[/FONT]
[FONT=Arial] ElseIf FileNme = Format(Date + 1, "m-d-yyyy") & "-Stock Prices_1.xlsm" Then[/FONT]
[FONT=Arial] ObjFile.Delete[/FONT]
[FONT=Arial] Else[/FONT]
[FONT=Arial] End If[/FONT]
<o:p></o:p>
[FONT=Arial]Next 'loops through each file[/FONT]
<o:p></o:p>
[FONT=Arial]Set ObjFile = Nothing[/FONT]
[FONT=Arial]Set objFolder = Nothing[/FONT]
[FONT=Arial]Set objFSO = Nothing[/FONT]
<o:p></o:p>
[FONT=Arial]End Sub[/FONT]
[FONT=Arial]‘+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++[/FONT]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]<o
></o>The Script File for upload to FTP site is here. Just add the code to a text file in Notepad and save with the .vbs name extension instead of the .txt extension:
<o
></o><o
></o>
Code:
[FONT=Arial][COLOR=black]Option Explicit[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]On Error Resume Next[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Main[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]Sub Main()[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]On Error Resume Next[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]'Declare objects[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim wsh 'As Wscript.Shell[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim tsScript 'As Scripting.TextStream[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim fs 'As Scripting.FileSystemObject[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]'Declare variables [/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strRemoteSite 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strUserName 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strPassword 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim intWindowStyle 'As Integer[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strFtpScriptFileName 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim n1 'As Integer[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim n2 'As Integer[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strCurrent_day 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strCurrent_mth 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strCurrent_yr 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strCurrent_YEAR 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Dim strCurrent_date 'As String[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]'Define constants[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Const TemporaryFolder = 2[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Const WshHide = 0[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Const WshNormalFocus = 1[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Const WshMinimizedFocus = 2[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Const WshMaximizedNoFocus = 3[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Const WshNormalNoFocus = 4[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Const WshMinimizedNoFocus = 6[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]'''''''''' Get and Set Date for string functions[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]n1 = MONTH(date())[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]n2 = DAY(date())[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]If len(n1) < 2 then[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strCurrent_mth = "0" & n1[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Else [/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strCurrent_mth = n1[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]End IF[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]If len(n2) < 2 then[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strCurrent_day = "0" & n2[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]Else [/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strCurrent_day = n2[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]End IF[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]strCurrent_yr = RIGHT(YEAR(date()),2)[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]strCurrent_YEAR = YEAR(date())[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]strCurrent_date = strCurrent_mth & strCurrent_day & strCurrent_yr[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] '''''''''' Create needed objects[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] Set wsh = CreateObject("Wscript.Shell")[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] Set fs = CreateObject("Scripting.FileSystemObject")[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] '''''''''' Set initial values[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strFtpScriptFileName = fs.BuildPath(fs.GetSpecialFolder(TemporaryFolder), fs.GetBaseName(Wscript.ScriptFullName) & ".script")[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] '''''''''' User info ''''''''''''''''''''''[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strPassword = "xxxzzz" 'FTP server password[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strUserName = "MyUserName" 'FTP server user name[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] strRemoteSite = "ftp.xxxzzzyyy.com" 'FTP server machine name[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] intWindowStyle = WshNormalFocus 'Just how visible? Use any Wsh constant defined above[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black]' CAUTION: If intWindowStyle is set to WshHide and user needs a dial-up connection, the dial-up[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]' dialog will be hidden, effectively hanging the program. If the connection is dialed before this[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]' program is run, there is no problem. Likewise no problem if user is on a LAN.[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] '''''''''' Set the window style[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] If Lcase(Right(Wscript.FullName, 11)) = "wscript.exe" Then[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] 'Running under WSCRIPT[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] If intWindowStyle <> WshHide Then[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] 'Need to switch to CSCRIPT[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] Wscript.CreateObject("Wscript.Shell").Run "cscript.exe """ & Wscript.ScriptFullName & """", intWindowStyle[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] Wscript.Quit[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] End If[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] End If[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] '''''''''' Start output script[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] Set tsScript = fs.CreateTextFile(strFtpScriptFileName, True)[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] tsScript.WriteLine "open " & strRemoteSite[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] tsScript.WriteLine strUserName[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] tsScript.WriteLine strPassword[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] tsScript.WriteLine "hash"[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] '''''''''' Identify files to upload[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] 'Just make a pair like the two examples below for every file you want to upload.[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] tsScript.WriteLine "ascii"[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] tsScript.WriteLine "put " & chr(34) &"T:\\Benefits\Executive Benefits\Our Stock\"& strCurrent_YEAR &" Daily Unit Val Calc\Company_"& strCurrent_date &".csv"& chr(34)[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] 'tsScript.WriteLine "binary"[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] 'tsScript.WriteLine "put " & """C:\Program Files\Accessories\HyperTerminal\CompuServe.ht"" " & "/users/john.doe/www/compuserve.ht"[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial][COLOR=black] '''''''''' Run the FTP script[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] tsScript.WriteLine "bye"[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] tsScript.Close[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] Set tsScript = Nothing[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] If fs.FileExists(fs.GetSpecialFolder(1) & "\ftp.exe") Then[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] wsh.Run fs.GetSpecialFolder(1) & "\ftp.exe -s:""" & strFtpScriptFileName & """", intWindowStyle, True[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] Else[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] wsh.Run fs.GetSpecialFolder(0) & "\ftp.exe -s:""" & strFtpScriptFileName & """", intWindowStyle, True[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] End If[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] 'wsh.Run "%windir%\ftp.exe -s:""" & strFtpScriptFileName & """", intWindowStyle, True[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black] fs.DeleteFile strFtpScriptFileName[/COLOR][/FONT][COLOR=black]<o:p></o:p>[/COLOR]
[FONT=Arial][COLOR=black]End Sub[/COLOR][/FONT]<o
></o>And the Batch File called to run the Script File for the FTP Upload is here. Just add the code to a text file in Notepad and save with the .bat file name extension instead of the .txt extension:
<o
></o><o
></o>
Code:
[COLOR=black]cscript C:\Windows\Scripts\FtpFileUpload.vbs //B[/COLOR]
