Trey31
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.Applic ation")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r isk 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").V alue = Me!Category
oSheet.Range("Report_Perio d").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_F inancial") .Value = Me!txtFinancial_Calculatio n
oSheet.Range("Risk_Score_O perational ").Value = Me!txtOperational_Composit e
oSheet.Range("Risk_Score_C ountry").V alue = Me!TxtCountry_Score_Calcul ation
oSheet.Range("Risk_Score_C ompliance" ).Value = Me!txtCompliance_Calculati on_Composi te
oSheet.Range("Composite_Ri sk_Score") .Value = Me!Composite_Risk_Score
oSheet.Range("Title_of_Ris k_Scorecar d").Value = Me!txtThird_Party_Name
iSheet.Range("Current_D_B_ SER_Score" ).Value = Me!Current_SER_Score
iSheet.Range("Third_Party_ Name_Finan cial").Val ue = 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
Dim xlApp As Object
Dim xlWB As Object
Dim oSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Applic
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r
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_
oSheet.Range("Category").V
oSheet.Range("Report_Perio
oSheet.Range("Tier").Value
oSheet.Range("Annual_Spend
oSheet.Range("Third_Party_
oSheet.Range("Risk_Score_F
oSheet.Range("Risk_Score_O
oSheet.Range("Risk_Score_C
oSheet.Range("Risk_Score_C
oSheet.Range("Composite_Ri
oSheet.Range("Title_of_Ris
iSheet.Range("Current_D_B_
iSheet.Range("Third_Party_
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
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
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
A:so redundant at the top:
Dim xlApp As Object
Dim xlWB As Object
Dim oSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Applic ation")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r isk 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.Applic ation")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r isk 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
Dim xlApp As Object
Dim xlWB As Object
Dim oSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Applic
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r
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.Applic
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\r
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
ASKER
I will try this......
Finally, there is no need to test for the existence of the same directory in two places...pick your spot.
oops - scratch that last comment...
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.
Are you sure you have permission to copy *from* the source directory?
ASKER
Yes I can do that.
Then either the file path or the file name is wrong.
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
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
btw: did you take bluelizard's advice?
And what does "Yes I can do that." mean in regards to file permissions?
And what does "Yes I can do that." mean in regards to file permissions?
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
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
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.
Ag, rockiroads: another set of eyes at this ungodly hour is much appreciated!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's 4:21 AM here in foggy Southern California. Sourdough bread just came out of the oven. Yum!
ASKER
Ok I will try this.........
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.Applic ation")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(sTemp lateFile)
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").V alue = Me!CATEGORY
oSheet.Range("Report_Perio d").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_F inancial") .Value = Me!txtFinancial_Calculatio n
oSheet.Range("Risk_Score_O perational ").Value = Me!txtOperational_Composit e
oSheet.Range("Risk_Score_C ountry").V alue = Me!TxtCountry_Score_Calcul ation
oSheet.Range("Risk_Score_C ompliance" ).Value = Me!txtCompliance_Calculati on_Composi te
oSheet.Range("Composite_Ri sk_Score") .Value = Me!Composite_Risk_Score
oSheet.Range("Title_of_Ris k_Scorecar d").Value = Me!txtThird_Party_Name
iSheet.Range("Current_D_B_ SER_Score" ).Value = Me!Current_SER_Score
iSheet.Range("Third_Party_ Name_Finan cial").Val ue = 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, 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.Applic
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(sTemp
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_
oSheet.Range("Category").V
oSheet.Range("Report_Perio
oSheet.Range("Tier").Value
oSheet.Range("Annual_Spend
oSheet.Range("Third_Party_
oSheet.Range("Risk_Score_F
oSheet.Range("Risk_Score_O
oSheet.Range("Risk_Score_C
oSheet.Range("Risk_Score_C
oSheet.Range("Composite_Ri
oSheet.Range("Title_of_Ris
iSheet.Range("Current_D_B_
iSheet.Range("Third_Party_
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:
ASKER
It WORKS! Thank you so much for being patient! Thank you both!
ASKER
Great Communication!
Well so as long as you understand the problem you faced
No worries - glad to help.
FileCopy "C:\Documents and Settings\csb231\My Documents\Projected Template V3.xls", "C:\risk scorecard\Projected Template V3.xls"
--bluelizard