Solved

FileCopy Error

Posted on 2007-11-19
25
298 Views
Last Modified: 2010-04-21
I have been working on this database all night and I am on the last step, and that is to export the query, create a folder and insert the excel template and excel file. I used the FileCopy method and it keeps saying that they cannot find that file. Please help. Here is what I have:

 Dim xlApp As Object
    Dim xlWB As Object
    Dim oSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
   
    Set xlWB = xlApp.Workbooks.Open("c:\risk scorecard\Projected Template V3.xls")
   
    If Dir("c:\risk scorecard\Projected Template V3.xls", vbDirectory) = "" Then
   
        If Dir("c:\risk scorecard\Projected Template V3.xls", vbDirectory) = "" Then
            FileCopy ("C:\Documents and Settings\csb231\My Documents\Projected Template V3.xls"), ("C:\risk scorecard\Projected Template V3.xls")
            IsValid = True
        End If
    End If
   
    Set oSheet = xlWB.Sheets("Composite Risk Score")
    Set iSheet = xlWB.Sheets("Financial Scoring")
    Set aSheet = xlWB.Sheets("Operational Scoring")
    Set eSheet = xlWB.Sheets("Country Risk")
    Set uSheet = xlWB.Sheets("Compliance Risk")
   
    oSheet.Range("Name_of_the_Supplier").Value = Me!txtThird_Party_Name
    oSheet.Range("Category").Value = Me!Category
    oSheet.Range("Report_Period").Value = Me![Risk Scorecard Qtr.]
    oSheet.Range("Tier").Value = Me!Tier
    oSheet.Range("Annual_Spend").Value = Me!Spend
    oSheet.Range("Third_Party_Manager").Value = Me!TP_Manager
    oSheet.Range("Risk_Score_Financial").Value = Me!txtFinancial_Calculation
    oSheet.Range("Risk_Score_Operational").Value = Me!txtOperational_Composite
    oSheet.Range("Risk_Score_Country").Value = Me!TxtCountry_Score_Calculation
    oSheet.Range("Risk_Score_Compliance").Value = Me!txtCompliance_Calculation_Composite
    oSheet.Range("Composite_Risk_Score").Value = Me!Composite_Risk_Score
    oSheet.Range("Title_of_Risk_Scorecard").Value = Me!txtThird_Party_Name
    iSheet.Range("Current_D_B_SER_Score").Value = Me!Current_SER_Score
    iSheet.Range("Third_Party_Name_Financial").Value = Me!txtThird_Party_Name

Exit_DTE:

    If Dir("C:\risk scorecard\", vbDirectory) = "" Then
   
        If Dir("C:\risk scorecard\", vbDirectory) = "" Then
            MkDir ("C:\risk scorecard\")
            IsValid = True
        End If
    End If
   
    xlWB.SaveAs "c:\risk scorecard\" & Me![Risk Scorecard Qtr.] & Me!txtThird_Party_Name & Me![Risk Scorecard] & ".xls"
    xlWB.Close
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
   
    Exit Sub
Err_DTE:
    MsgBox Err.Description
    Resume Exit_DTE:
   
End Sub
0
Comment
Question by:Trey31
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 6
  • +1
25 Comments
 
LVL 14

Expert Comment

by:bluelizard
ID: 20311562
(maybe not the solution, but anyway): why do you put the parameters of FileCopy in parens?  try:

FileCopy "C:\Documents and Settings\csb231\My Documents\Projected Template V3.xls", "C:\risk scorecard\Projected Template V3.xls"


--bluelizard
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311585
Now you are being redundant:

Exit_DTE:

    If Dir("C:\risk scorecard\", vbDirectory) = "" Then
   
        If Dir("C:\risk scorecard\", vbDirectory) = "" Then
            MkDir ("C:\risk scorecard\")
            IsValid = True
        End If
    End If

Surely you meant:

Exit_DTE:

    If Dir("C:\risk scorecard\", vbDirectory) = "" Then
            MkDir ("C:\risk scorecard\")
            IsValid = True
    End If
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311587
A:so redundant at the top:

Dim xlApp As Object
    Dim xlWB As Object
    Dim oSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
   
    Set xlWB = xlApp.Workbooks.Open("c:\risk scorecard\Projected Template V3.xls")
   
    If Dir("c:\risk scorecard\Projected Template V3.xls", vbDirectory) = "" Then
   
        If Dir("c:\risk scorecard\Projected Template V3.xls", vbDirectory) = "" Then
            FileCopy ("C:\Documents and Settings\csb231\My Documents\Projected Template V3.xls"), ("C:\risk scorecard\Projected Template V3.xls")
            IsValid = True
        End If
    End If

should be:

Dim xlApp As Object
    Dim xlWB As Object
    Dim oSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
   
    Set xlWB = xlApp.Workbooks.Open("c:\risk scorecard\Projected Template V3.xls")
   
    If Dir("c:\risk scorecard\Projected Template V3.xls", vbDirectory) = "" Then
            FileCopy ("C:\Documents and Settings\csb231\My Documents\Projected Template V3.xls"), ("C:\risk scorecard\Projected Template V3.xls")
            IsValid = True
    End If
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Trey31
ID: 20311590
I will try this......
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311591
Finally, there is no need to test for the existence of the same directory in two places...pick your spot.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311595
oops - scratch that last comment...
0
 

Author Comment

by:Trey31
ID: 20311618
It still gives me the error that it cannot find the template file. I have everything in the right spot. I must be doing something wrong with the code.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311649
Are you sure you have permission to copy *from* the source directory?
0
 

Author Comment

by:Trey31
ID: 20311659
Yes I can do that.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311666
Then either the file path or the file name is wrong.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 20311670
Just wondering about this

If Dir("c:\risk scorecard\Projected Template V3.xls", vbDirectory) = "" Then


Surely you shouldnt put filenames to check to see if directory exists?

I would say this always returns null



0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311674
btw: did you take bluelizard's advice?

And what does "Yes I can do that." mean in regards to file permissions?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 20311684
I'd probably do the code like this

Dim sSourceFile as String
Dim sDestDir as String

sSourceFile = "C:\Documents and Settings\csb231\My Documents\Projected Template V3.xls"
sDestDir = "C:\risk scorecard"

'Check source file exists
if dir$(sSourceFile) = "" then
    msgbox "Source File Not Found"
else
    'Make dest dir if required
    If Dir(sDestDir, vbDirectory) = "" Then
        mkdir sDestDir
    end if

    FileCopy sSourceFile, sDestDir & "\Projected Template V3.xls"
end if


0
 

Author Comment

by:Trey31
ID: 20311685
Yes I took the parens off and I do have the rights to copy this file. I have tried this, i know it has to be 100 times, I cant get past it. All it gives me is an error saying that they cannot find that file.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311686
Ag, rockiroads: another set of eyes at this ungodly hour is much appreciated!
0
 
LVL 29

Accepted Solution

by:
Badotz earned 250 total points
ID: 20311725
What is the error, exactly. Word for word, number for number, line for line.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 20311733
Howdo badotz - thanks for the timely reminder - its lunchtime. Chicken pasta here I come :)
So is it late for you or what then


Trey31, try do some defensive programming, always check for files/dirs first. May help you solve problems.
Also worth capturing error

msgbox err.description

to see proper error message
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 20311743
ah ha, just saw something

you are opening the file then doing a copy

well the copy will fail because the file is open

therefore do the copy first then open the file

'change this line also to use variables you define
Set xlWB = xlApp.Workbooks.Open("c:\risk scorecard\Projected Template V3.xls")
to
Set xlWB = xlApp.Workbooks.Open(sDestDir & "\Projected Template V3.xls")

or create a field called sDestFile or something which holds the full path
that way, if you change the name, you only change in one place and not thruout the code


0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311768
It's 4:21 AM here in foggy Southern California. Sourdough bread just came out of the oven. Yum!
0
 

Author Comment

by:Trey31
ID: 20311788
Ok I will try this.........
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 20311793
Early morning aint it? I was supposed to be coming to LA but had to cancel my holiday. Could of done with that as its cold here. Snowed last nite!


trey31, an example of what I mean


    Dim xlApp As Object
    Dim xlWB As Object
    Dim sSourceFile As String
    Dim sDestDir As String
    Dim sTemplateFile As String
    Dim oSheet As Excel.Worksheet
    Dim sSaveFile As String

   
    sSourceFile = "C:\Documents and Settings\csb231\My Documents\Projected Template V3.xls"
    sDestDir = "C:\risk scorecard"
    sTemplateFile = sDestDir & "\Projected Template V3.xls"

    'Check source file exists
    If Dir$(sSourceFile) = "" Then
        MsgBox "Source Template File Not Found"
        Exit Sub
    Else
        'Make dest dir if required
        If Dir(sDestDir, vbDirectory) = "" Then
            MkDir sDestDir
        End If

        FileCopy sSourceFile, sTemplateFile
    End If
   
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
   
    Set xlWB = xlApp.Workbooks.Open(sTemplateFile)
   
    Set oSheet = xlWB.Sheets("Composite Risk Score")
    Set iSheet = xlWB.Sheets("Financial Scoring")
    Set aSheet = xlWB.Sheets("Operational Scoring")
    Set eSheet = xlWB.Sheets("Country Risk")
    Set uSheet = xlWB.Sheets("Compliance Risk")
   
    oSheet.Range("Name_of_the_Supplier").Value = Me!txtThird_Party_Name
    oSheet.Range("Category").Value = Me!CATEGORY
    oSheet.Range("Report_Period").Value = Me![Risk Scorecard Qtr.]
    oSheet.Range("Tier").Value = Me!Tier
    oSheet.Range("Annual_Spend").Value = Me!Spend
    oSheet.Range("Third_Party_Manager").Value = Me!TP_Manager
    oSheet.Range("Risk_Score_Financial").Value = Me!txtFinancial_Calculation
    oSheet.Range("Risk_Score_Operational").Value = Me!txtOperational_Composite
    oSheet.Range("Risk_Score_Country").Value = Me!TxtCountry_Score_Calculation
    oSheet.Range("Risk_Score_Compliance").Value = Me!txtCompliance_Calculation_Composite
    oSheet.Range("Composite_Risk_Score").Value = Me!Composite_Risk_Score
    oSheet.Range("Title_of_Risk_Scorecard").Value = Me!txtThird_Party_Name
    iSheet.Range("Current_D_B_SER_Score").Value = Me!Current_SER_Score
    iSheet.Range("Third_Party_Name_Financial").Value = Me!txtThird_Party_Name

Exit_DTE:

    sSaveFile = sDestDir & "\" & Me![Risk Scorecard Qtr.] & Me!txtThird_Party_Name & Me![Risk Scorecard] & ".xls"
    MsgBox "Saving to " & sSaveFile
    xlWB.SaveAs sSaveFile
    xlWB.Close
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
   
    Exit Sub
Err_DTE:
    MsgBox Err.Description
    Resume Exit_DTE:
0
 

Author Comment

by:Trey31
ID: 20311847
It WORKS! Thank you so much for being patient! Thank you both!
0
 

Author Closing Comment

by:Trey31
ID: 31409910
Great Communication!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 20311875
Well so as long as you understand the problem you faced
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20311930
No worries - glad to help.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question