Scripting Excel to populate a spreadsheet with one word 
Author Message
 Scripting Excel to populate a spreadsheet with one word

First off, let me say "Thank You" to all the people who have helped me in
this new endeavor of scripting!  I really mean it!  Thanks!

This ought to be an easy one for you guys...

I am trying to write a script that will create a folder on the users local
harddrive (C:\WSH2), then create 2 folders within that folder (C:\WSH2\COMP
and C:\WSH2\FILES) and so far the script accomplishes this.  My problem is
that I need to create a simple "test" spreadsheet populated with the word
"COMPLETED".  Word then grabs the word from a predetermined field and
inserts it into a sentence and saves the document to C:\WSH2 as
"Word_Test_Completed.doc" (This also works in the larger script).
Everything works except the create new spreadsheet part.  I've included the
code that I use below...any ideas?

****************************************************************

Dim objFSO
Dim objFolder1
Dim objFolder2
Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objFolder1 = objFSO.CreateFolder("c:\WSH2")
Set objFolder2 = objFolder1.Subfolders.Add("COMP")
Set objFolder2 = objFolder1.Subfolders.Add("FILES")
Sub Test()
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "COMPLETED"
    Range("A2:G49").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.Copy
    Range("A2:A43").Select
    ActiveSheet.Paste
    Range("B1:B43").Select
    ActiveSheet.Paste
    Range("C1:C43").Select
    ActiveSheet.Paste
    Range("D1:D43").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ChDir "C:\WSH2\COMP"
    ActiveWorkbook.SaveAs.Filename "C:\WSH2\COMP\Test.xls", FileFormat = _
        xlNormal, Password = "", WriteResPassword = "", ReadOnlyRecommended
= False _
        , CreateBackup = False
End Sub

***************************************************************

CJ



Sat, 02 Oct 2004 22:21:08 GMT  
 Scripting Excel to populate a spreadsheet with one word

Hi CJ,

You haven't created an Excel object in your script?  You can't just paste
code directly from an Excel macro into VBScript and expect it to work.  You
have to prefix the Excel commands with the object (or use a With block).
Here's a small example VBS that should help get you started.

Gordon Bell

Dim xlApp
Dim xlBook
Dim xlSheet

Call Test()

Sub Test()
    Set xlApp = WScript.CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add()
    Set xlSheet = xlBook.Sheets(1)

    xlSheet.Range("A1") = "COMPLETED"

    With xlSheet.Range("A1").Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With

    ' Unremark next line to save spreadsheet
    ''''xlBook.SaveAs.Filename "C:\WSH2\COMP\Test.xls"

    xlApp.Visible = True
End Sub


First off, let me say "Thank You" to all the people who have helped me in
this new endeavor of scripting!  I really mean it!  Thanks!

This ought to be an easy one for you guys...

I am trying to write a script that will create a folder on the users local
harddrive (C:\WSH2), then create 2 folders within that folder (C:\WSH2\COMP
and C:\WSH2\FILES) and so far the script accomplishes this.  My problem is
that I need to create a simple "test" spreadsheet populated with the word
"COMPLETED".  Word then grabs the word from a predetermined field and
inserts it into a sentence and saves the document to C:\WSH2 as
"Word_Test_Completed.doc" (This also works in the larger script).
Everything works except the create new spreadsheet part.  I've included the
code that I use below...any ideas?

****************************************************************

Dim objFSO
Dim objFolder1
Dim objFolder2
Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objFolder1 = objFSO.CreateFolder("c:\WSH2")
Set objFolder2 = objFolder1.Subfolders.Add("COMP")
Set objFolder2 = objFolder1.Subfolders.Add("FILES")
Sub Test()
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "COMPLETED"
    Range("A2:G49").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.Copy
    Range("A2:A43").Select
    ActiveSheet.Paste
    Range("B1:B43").Select
    ActiveSheet.Paste
    Range("C1:C43").Select
    ActiveSheet.Paste
    Range("D1:D43").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ChDir "C:\WSH2\COMP"
    ActiveWorkbook.SaveAs.Filename "C:\WSH2\COMP\Test.xls", FileFormat = _
        xlNormal, Password = "", WriteResPassword = "", ReadOnlyRecommended
= False _
        , CreateBackup = False
End Sub

***************************************************************

CJ



Sun, 03 Oct 2004 00:36:52 GMT  
 Scripting Excel to populate a spreadsheet with one word
Gordon,
I knew I was doing something wrong!  Sometimes it hurts being a newbie! =)
LOL

Thanks!

CJ

Quote:

> Hi CJ,

> You haven't created an Excel object in your script?  You can't just paste
> code directly from an Excel macro into VBScript and expect it to work.
You
> have to prefix the Excel commands with the object (or use a With block).
> Here's a small example VBS that should help get you started.

> Gordon Bell

> Dim xlApp
> Dim xlBook
> Dim xlSheet

> Call Test()

> Sub Test()
>     Set xlApp = WScript.CreateObject("Excel.Application")
>     Set xlBook = xlApp.Workbooks.Add()
>     Set xlSheet = xlBook.Sheets(1)

>     xlSheet.Range("A1") = "COMPLETED"

>     With xlSheet.Range("A1").Font
>         .Name = "Arial"
>         .Size = 8
>         .Strikethrough = False
>         .Superscript = False
>         .Subscript = False
>         .OutlineFont = False
>         .Shadow = False
>         .Underline = xlUnderlineStyleNone
>         .ColorIndex = xlAutomatic
>     End With

>     ' Unremark next line to save spreadsheet
>     ''''xlBook.SaveAs.Filename "C:\WSH2\COMP\Test.xls"

>     xlApp.Visible = True
> End Sub



> First off, let me say "Thank You" to all the people who have helped me in
> this new endeavor of scripting!  I really mean it!  Thanks!

> This ought to be an easy one for you guys...

> I am trying to write a script that will create a folder on the users local
> harddrive (C:\WSH2), then create 2 folders within that folder
(C:\WSH2\COMP
> and C:\WSH2\FILES) and so far the script accomplishes this.  My problem is
> that I need to create a simple "test" spreadsheet populated with the word
> "COMPLETED".  Word then grabs the word from a predetermined field and
> inserts it into a sentence and saves the document to C:\WSH2 as
> "Word_Test_Completed.doc" (This also works in the larger script).
> Everything works except the create new spreadsheet part.  I've included
the
> code that I use below...any ideas?

> ****************************************************************

> Dim objFSO
> Dim objFolder1
> Dim objFolder2
> Set objFSO = CreateObject("Scripting.FileSystemObject")
>  Set objFolder1 = objFSO.CreateFolder("c:\WSH2")
> Set objFolder2 = objFolder1.Subfolders.Add("COMP")
> Set objFolder2 = objFolder1.Subfolders.Add("FILES")
> Sub Test()
>     Cells.Select
>     With Selection.Font
>         .Name = "Arial"
>         .Size = 8
>         .Strikethrough = False
>         .Superscript = False
>         .Subscript = False
>         .OutlineFont = False
>         .Shadow = False
>         .Underline = xlUnderlineStyleNone
>         .ColorIndex = xlAutomatic
>     End With
>     Range("A1").Select
>     ActiveCell.FormulaR1C1 = "COMPLETED"
>     Range("A2:G49").Select
>     ActiveSheet.Paste
>     Range("A1").Select
>     Selection.Copy
>     Range("A2:A43").Select
>     ActiveSheet.Paste
>     Range("B1:B43").Select
>     ActiveSheet.Paste
>     Range("C1:C43").Select
>     ActiveSheet.Paste
>     Range("D1:D43").Select
>     ActiveSheet.Paste
>     Application.CutCopyMode = False
>     ChDir "C:\WSH2\COMP"
>     ActiveWorkbook.SaveAs.Filename "C:\WSH2\COMP\Test.xls", FileFormat = _
>         xlNormal, Password = "", WriteResPassword = "",
ReadOnlyRecommended
> = False _
>         , CreateBackup = False
> End Sub

> ***************************************************************

> CJ



Sun, 03 Oct 2004 03:09:29 GMT  
 Scripting Excel to populate a spreadsheet with one word
Just an FYI:
Here is the actual code I used to complete this portion of the test...

*******************************************
Dim objFSO
Dim objFolder1
Dim xlApp
Dim xlBook
Dim xlSheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder1 = objFSO.CreateFolder("c:\WSH")

Call Test()

Sub Test()
    Set xlApp = WScript.CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add()
    Set xlSheet = xlBook.Sheets(1)

    xlSheet.Range("A1:F1") = "COMPLETED"
    xlSheet.Range("A2:F2") = "COMPLETED"
    xlSheet.Range("A3:F3") = "COMPLETED"
    xlSheet.Range("A4:F4") = "COMPLETED"
    xlSheet.Range("A5:F5") = "COMPLETED"
    xlSheet.Range("A6:F6") = "COMPLETED"
    xlSheet.Range("A7:F7") = "COMPLETED"
    xlSheet.Range("A8:F8") = "COMPLETED"
    xlSheet.Range("A9:F9") = "COMPLETED"
    xlSheet.Range("A10:F10") = "COMPLETED"
    xlSheet.Range("A11:F11") = "COMPLETED"
    xlSheet.Range("A12:F12") = "COMPLETED"
    xlSheet.Range("A13:F13") = "COMPLETED"
    xlSheet.Range("A14:F14") = "COMPLETED"
    xlSheet.Range("A15:F15") = "COMPLETED"
    xlSheet.Range("A16:F16") = "COMPLETED"
    xlSheet.Range("A17:F17") = "COMPLETED"
    xlSheet.Range("A18:F18") = "COMPLETED"
    xlSheet.Range("A19:F19") = "COMPLETED"
    xlSheet.Range("A20:F20") = "COMPLETED"
    xlSheet.Range("A21:F21") = "COMPLETED"
    xlSheet.Range("A22:F22") = "COMPLETED"
    xlSheet.Range("A23:F23") = "COMPLETED"
    xlSheet.Range("A24:F24") = "COMPLETED"
    With xlSheet.Range("A1").Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ColorIndex = xlAutomatic
    End With
    xlApp.Visible = True
    xlApp.ActiveWorkbook.SaveAs "C:\WSH\Test.xls"
    xlApp.UserControl = False
    xlApp.Quit
End Sub

****************************************************************************
*****

Quote:

> First off, let me say "Thank You" to all the people who have helped me in
> this new endeavor of scripting!  I really mean it!  Thanks!

> This ought to be an easy one for you guys...

> I am trying to write a script that will create a folder on the users local
> harddrive (C:\WSH2), then create 2 folders within that folder
(C:\WSH2\COMP
> and C:\WSH2\FILES) and so far the script accomplishes this.  My problem is
> that I need to create a simple "test" spreadsheet populated with the word
> "COMPLETED".  Word then grabs the word from a predetermined field and
> inserts it into a sentence and saves the document to C:\WSH2 as
> "Word_Test_Completed.doc" (This also works in the larger script).
> Everything works except the create new spreadsheet part.  I've included
the
> code that I use below...any ideas?

> ****************************************************************

> Dim objFSO
> Dim objFolder1
> Dim objFolder2
> Set objFSO = CreateObject("Scripting.FileSystemObject")
>  Set objFolder1 = objFSO.CreateFolder("c:\WSH2")
> Set objFolder2 = objFolder1.Subfolders.Add("COMP")
> Set objFolder2 = objFolder1.Subfolders.Add("FILES")
> Sub Test()
>     Cells.Select
>     With Selection.Font
>         .Name = "Arial"
>         .Size = 8
>         .Strikethrough = False
>         .Superscript = False
>         .Subscript = False
>         .OutlineFont = False
>         .Shadow = False
>         .Underline = xlUnderlineStyleNone
>         .ColorIndex = xlAutomatic
>     End With
>     Range("A1").Select
>     ActiveCell.FormulaR1C1 = "COMPLETED"
>     Range("A2:G49").Select
>     ActiveSheet.Paste
>     Range("A1").Select
>     Selection.Copy
>     Range("A2:A43").Select
>     ActiveSheet.Paste
>     Range("B1:B43").Select
>     ActiveSheet.Paste
>     Range("C1:C43").Select
>     ActiveSheet.Paste
>     Range("D1:D43").Select
>     ActiveSheet.Paste
>     Application.CutCopyMode = False
>     ChDir "C:\WSH2\COMP"
>     ActiveWorkbook.SaveAs.Filename "C:\WSH2\COMP\Test.xls", FileFormat = _
>         xlNormal, Password = "", WriteResPassword = "",
ReadOnlyRecommended
> = False _
>         , CreateBackup = False
> End Sub

> ***************************************************************

> CJ



Sun, 03 Oct 2004 05:05:15 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Access Table to Excel Spreadsheet - in one hundred words or less...

2. Reading a Excel spreadsheet

3. Web Form to Excel Spreadsheet

4. using data in an excel spreadsheet

5. Access an excel spreadsheet through asp?

6. Read Excel Spreadsheet in VBScript using ADO

7. newbie question: generating Excel spreadsheet from the web

8. Using either a CSV or Excel Spreadsheet in VBScript

9. reverse engineer an existing excel spreadsheet using WSH

10. Convert Excel or Access spreadsheet to INI file format

11. Excel Spreadsheet links

12. Write results from SQL Statement to an Excel Spreadsheet

 

 
Powered by phpBB® Forum Software