Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FileCopy (Outlook 98, Excel 97)

    I have an Outlook for with VB copy in it. I am trying to copy file 1 to file 2. THe files are located on a network drive. This was working until I added the Filecopy line.

    Call Excel
    Sub cmdExcel_click()
    Filecopy "tabsproddatabaseedgarsecuritymastertest.xls", "tabsproddatabaseedgarsecuritytest.xls"
    Set objWSHShell = CreateObject("WScript.Shell")
    objWSHShell.Run("excel.exe tabsproddatabaseedgarsecurityTEST.xls ")
    end sub

    It is giving me a Type Mismatch. Can anyone tell me what I am doing wrong.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy (Outlook 98, Excel 97)

    I have changed the code to the sample below. But I am now getting "Expected end of statement" and it points to the 15th line (Dim fso As Scripting.......... )


    '-=-=-=-=-=-=-=-=-=-=-=-=-
    'Call Excel
    Sub cmdExcel_click()
    Working
    update1
    update2
    call fsoCopyFiles("J:edgarsecuritymastertest.xls", "J:edgarsecuritytest.xls")
    'FileCopy "J:edgarsecuritymastertest.xls", "J:edgarsecuritytest.xls"
    Set objWSHShell = CreateObject("WScript.Shell")
    objWSHShell.Run("excel.exe tabsproddatabaseedgarsecurityTEST.xls")
    end sub
    '-=-=-=-=-=-=-=-=-=-=-=-=-

    Sub fsoCopyFiles(strPath1, strPath2)
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    fso.CopyFile strPath1, strPath2
    End Sub

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: FileCopy (Outlook 98, Excel 97)

    Are you running this in a VBA or VBScript environment? In VBScript, you cannot declare variable types; they are all variants. In brief, just say

    Dim fso

    This makes it a huge pain to develop in one environment and run in the other. It might be helpful to try a VBScript editor. I thought there was one called MSE, but I recently changed computers and have no idea where it came from.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy (Outlook 98, Excel 97)

    Yes I am running it from MS Excel97 macro vbscript

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Washington, USA
    Posts
    750
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy (Outlook 98, Excel 97)

    JScher's right about the dim problem. But I think you're supposed to pass the argument as one string. But Outlook environment is a bit different than the other Office products, so I could be wrong. That said, you might try something like:

    f1 = "J:edgarsecuritymastertest.xls"
    f2 = "J:edgarsecuritytest.xls"
    f3 = f1 & ", " & f2

    call fsoCopyFiles( f3 )
    .
    .
    .

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy (Outlook 98, Excel 97)

    I tried the change you gave and it runs with our an error but the file (Mastertest) is not copied to test. Am I doing something wrong?

    '-=-=-=-=-=-=-=-=-=-=-=-=-
    'Call Excel
    Sub cmdExcel_click()
    Working
    update1
    update2
    f1 = "J:edgarsecuritymastertest.xls"
    f2 = "J:edgarsecuritytest.xls"
    f3 = f1 & ", " & f2
    call fsoCopyFiles(f3)
    Set objWSHShell = CreateObject("WScript.Shell")
    objWSHShell.Run("excel.exe tabsproddatabaseedgarsecurityTEST.xls")
    end sub
    '-=-=-=-=-=-=-=-=-=-=-=-=-

    Sub fsoCopyFiles(f3)
    CopyFile=(f3)
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •