File copy problem (1 Viewer)

[LooneyLou]

Hi Guys

I have a problem with copying an Excel document to a different folder within Access, using the following 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

 

[moke123]

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]

Hi Moke

I have no idea how to implement your suggestions.

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

 

[LooneyLou]

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]

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.

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

 

[Gasman]

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

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]

Your code shows .xlxs as the file extension but it should be .xlsx

 

[psyc0tic1]

    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:

    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]

Brilliant spot psyc0tic1

Which explains why .txt files and others worked.

Shouldn't that be:

    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]"

 

[psyc0tic1]

Did I actually help someone for a change?!!

 

[Gasman]

Did I actually help someone for a change?!!

Yes, it is time to give back

 

[moke123]

louise,
You didnt answer the question about option explicit.

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]

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]

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-explicit-statement

Also DIM your variables with the correct type

Dim Files, Folderpath, AnotherVariable as String

only defines AnotherVariable as a string the rest are variants. So you should use

Dim Files as String, Folderpath as String, AnotherVariable as String

Debug.Print
https://bytes.com/topic/access/answers/915326-how-use-debug-print

 

[moke123]

At the top of every module you should have

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]

Thanks guys for your responses and links. I'll go and have a play

Louise