File copy problem (1 Viewer)

LooneyLou

New member
Local time
Today, 02:47
Joined
Nov 13, 2018
Messages
5
Hi Guys

I have a problem with copying an Excel document to a different folder within Access, using the following code.

Code:
Private Sub btnMeasure_Click()
Dim Files
    msCustomer = Me.fldCustomer
    msJob = Me.fldJob
    msMethod = Me.fldCastingMethod
    msDate = Year(Date)
    msDateFull = Format(Date, "yyyy-mm-dd")
    FileStructure = "\\CMASERVER\Data\Louise\SLA\" & msCustomer & "\" & msDate & "\" & msDateFull & " - " & msJob & "\Measurement Report\" & msMethod & " Measurement Report.xlxs"
    FileMove = "\\CMASERVER\Data\Louise\SLA\Report Masters\" & msMethod & " Measurement Report.xlxs"

FileCopy FileMove, FileStructure

End Sub

The code works if I move a .txt file, but I get a Run-time error 53, file not found when trying to move a .xlxs file.
Is there a special way for copying Excel files that I'm missing?

Regards
Louise
 
Last edited:

moke123

AWF VIP
Local time
Yesterday, 22:47
Joined
Jan 11, 2013
Messages
3,834
I would try adding a debug.print to insure that the file paths are resolving correctly.

Do you have option explicit declared?
looks like you should get an error as none of your variables are dimmed.
 

LooneyLou

New member
Local time
Today, 02:47
Joined
Nov 13, 2018
Messages
5
Hi Moke

I have no idea how to implement your suggestions. :confused:

I tried, successfully moving jpeg, mp3, pdf and solidworks files called "CAD Measurement Report" from the FileMove dir to the FileStructure dir.
 

LooneyLou

New member
Local time
Today, 02:47
Joined
Nov 13, 2018
Messages
5
Found out I can move old xls file types but not xlsx, have no idea why this is the case. Just going to convert and rework all my excel files into 97-2003 worksheets.
 

sxschech

Registered User.
Local time
Yesterday, 19:47
Joined
Mar 2, 2010
Messages
789
You might try to add quotes around the file name
such as "C:\Temp\My Poorly Named File 2018-11-12.xlsx"
I used Chr(34) to add the quote characters in the example below.

Code:
stFile = Chr(34) & "C:\Temp\My Poorly Named File 2018-11-12.xlsx" & Chr(34)
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:47
Joined
Sep 21, 2011
Messages
13,964
Found out I can move old xls file types but not xlsx, have no idea why this is the case. Just going to convert and rework all my excel files into 97-2003 worksheets.

Something wrong there.

To debug print.
Open the code window (Alt + F11)
Make sure the Immediate window is visible
Locate your form and then the sub in the form
Add
Code:
Debug.Print
after your filenames and before the copy line.

Run the code as you normally would, then go back to the code window and see what is displayed in the immediate window.

HTH
 

psyc0tic1

Access Moron
Local time
Yesterday, 21:47
Joined
Jul 10, 2017
Messages
360
Your code shows .xlxs as the file extension but it should be .xlsx
 

psyc0tic1

Access Moron
Local time
Yesterday, 21:47
Joined
Jul 10, 2017
Messages
360
Code:
    FileStructure = "\\CMASERVER\Data\Louise\SLA\" & msCustomer & "\" & msDate & "\" & msDateFull & " - " & msJob & "\Measurement Report\" & msMethod & " Measurement Report[COLOR="red"].xlxs[/COLOR]"
    FileMove = "\\CMASERVER\Data\Louise\SLA\Report Masters\" & msMethod & " Measurement Report[COLOR="Red"].xlxs[/COLOR]"
Shouldn't that be:
Code:
    FileStructure = "\\CMASERVER\Data\Louise\SLA\" & msCustomer & "\" & msDate & "\" & msDateFull & " - " & msJob & "\Measurement Report\" & msMethod & " Measurement Report[COLOR="SeaGreen"].xlsx[/COLOR]"
    FileMove = "\\CMASERVER\Data\Louise\SLA\Report Masters\" & msMethod & " Measurement Report[COLOR="SeaGreen"].xlsx[/COLOR]"
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:47
Joined
Sep 21, 2011
Messages
13,964
Brilliant spot psyc0tic1 :cool:

Which explains why .txt files and others worked.:)

Shouldn't that be:
Code:
    FileStructure = "\\CMASERVER\Data\Louise\SLA\" & msCustomer & "\" & msDate & "\" & msDateFull & " - " & msJob & "\Measurement Report\" & msMethod & " Measurement Report[COLOR="SeaGreen"].xlsx[/COLOR]"
    FileMove = "\\CMASERVER\Data\Louise\SLA\Report Masters\" & msMethod & " Measurement Report[COLOR="SeaGreen"].xlsx[/COLOR]"
 

moke123

AWF VIP
Local time
Yesterday, 22:47
Joined
Jan 11, 2013
Messages
3,834
louise,
You didnt answer the question about option explicit.

Code:
Dim Files
    msCustomer = Me.fldCustomer
    msJob = Me.fldJob
    msMethod = Me.fldCastingMethod
    msDate = Year(Date)
    msDateFull = Format(Date, "yyyy-mm-dd")
    FileStructure = "\\CMASERVER\Data\Louise\SLA\" & msCustomer & "\" & msDate & "\" & msDateFull & " - " & msJob & "\Measurement Report\" & msMethod & " Measurement Report.xlxs"
    FileMove = "\\CMASERVER\Data\Louise\SLA\Report Masters\" & msMethod & " Measurement Report.xlxs"
You dim Files (as a variant but you dont use it) but never dim msCustomer, msJob, msMethod, etc.

you should really have option explicit declared in all your modules.
 

LooneyLou

New member
Local time
Today, 02:47
Joined
Nov 13, 2018
Messages
5
Thanks psyc0tic1

I noticed it this afternoon :banghead:
Lesson learnt. Proof read and don't spend all day attempting to write VBA code :)

Gasman
I couldn't get the Debug.print code to run. I need to read up on it more.

Moke123
I still have no Idea what you mean, could you give me an example or point me the right direction for further reading. I'm rather new to all this :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:47
Joined
Sep 21, 2011
Messages
13,964

moke123

AWF VIP
Local time
Yesterday, 22:47
Joined
Jan 11, 2013
Messages
3,834
At the top of every module you should have

Code:
Option Compare Database
Option Explicit

option explicit forces you to declare your variables and will catch any errors.

see this article to get you started and it tells you how to have it automatically added. https://wellsr.com/vba/excel/vba-option-explicit/
the article is for excel but it applies the same to access.
 

LooneyLou

New member
Local time
Today, 02:47
Joined
Nov 13, 2018
Messages
5
Thanks guys for your responses and links. I'll go and have a play :)

Louise
 

Users who are viewing this thread

Top Bottom