asked on

FileCopy Error

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

bluelizard

(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

Badotz

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

Badotz

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

Trey31

ASKER

I will try this......

Badotz

Finally, there is no need to test for the existence of the same directory in two places...pick your spot.

Badotz

oops - scratch that last comment...

Trey31

ASKER

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.

Badotz

Are you sure you have permission to copy *from* the source directory?

Trey31

ASKER

Yes I can do that.

Badotz

Then either the file path or the file name is wrong.

rockiroads

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

Badotz

btw: did you take bluelizard's advice?

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

rockiroads

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

Trey31

ASKER

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.

Badotz

Ag, rockiroads: another set of eyes at this ungodly hour is much appreciated!

ASKER CERTIFIED SOLUTION

Badotz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

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

SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Badotz

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

Trey31

ASKER

Ok I will try this.........

rockiroads

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:

Trey31

ASKER

It WORKS! Thank you so much for being patient! Thank you both!

Trey31

ASKER

Great Communication!

rockiroads

Well so as long as you understand the problem you faced

Badotz

No worries - glad to help.