Run-Time error '13' Type Mismatch

[ddrew]

Hi im getting an error on a line from my export code, but I cant figure out whats wrong. The line in red is wheer the debug points to.

Private Sub btnExportTrainingRecord_Click()
    Dim strday As String    'The date
    Dim sDest As String    'Where the file will be copied to
    Dim sSource As String    'The name of the file to be copied

    Dim strBackUpDir As String
    Dim strBackUpFolder As String
    Dim strBackUpDogNameFolder As String

    Dim strDogName As String    'The name of the dog that is geting exported


    strBackUpDir = "c:\GPandDetectionDogTrainingLogBackUp\"
    strBackUpFolder = "c:\GPandDetectionDogTrainingLogBackUp\Training"
    strBackUpDogNameFolder = "c:\GPandDetectionDogTrainingLogBackUp\Training\" & Forms![frm_Profile]![DogName]

    If Len(Dir(strBackUpDir, vbDirectory)) = 0 Then
        MkDir strBackUpDir
    End If

    If Len(Dir(strBackUpFolder, vbDirectory)) = 0 Then
        MkDir strBackUpFolder
    End If

    If Len(Dir(strBackUpDogNameFolder, vbDirectory)) = 0 Then
        MkDir strBackUpDogNameFolder
    End If


    Shell "EXPLORER.EXE " & strBackUpDogNameFolder, vbNormalFocus

    strday = Format(Now(), "_yyyy_mm_dd")
    [COLOR="Red"]sSource = "c:\GPandDetectionDogTrainingLogBackUp\Training\GPandDetectionDogTrainingLogBackUpTraining\" & strBackUpDogNameFolder \ "GPandDetectionDogTrainingLogBackUpTraining"[/COLOR]
strDogName = Forms![frm_Profile]![DogName]
    sDest = "c:\GPandDetectionDogTrainingLogBackUp\Training\GPandDetectionDogTrainingLogBackUpTraining\" & strBackUpDogNameFolder \ "GPandDetectionDogTrainingLogBackUpTraining" & strDogName & strday & ".xls"

    'Copies the files to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", "c:\GPandDetectionDogTrainingLogBackUp\Training\" & strBackUpDogNameFolder \ "GPandDetectionDogTrainingLogBackUpTraining.xls", True

    'Copies and renames the file
    FileCopy sSource, sDest

End Sub

 

[bob fitz]

I haven't studied your code but perhaps it should be:
sSource = "c:\GPandDetectionDogTrainingLogBackUp\Training\GPandDetectionDogTrainingLogBackUpTraining\" & strBackUpDogNameFolder & "\GPandDetectionDogTrainingLogBackUpTraining"

 

[ddrew]

Well that worked but I now have a problem in as far as it wont create the file, the debug comes up on this line

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", "c:\GPandDetectionDogTrainingLogBackUp\Training\" & strBackUpDogNameFolder & "\GPandDetectionDogTrainingLogBackUpTraining.xls", True

 

[bob fitz]

Does it give any message or error number

 

[ddrew]

Does it give any message or error number

Yes, sorry,

Run-time error '3436':
Failure creating file

Each of the folders gets created (if they havent been already) but the actual spreadsheet isnt.

I've complicated it a bit by each dog having its own backup (there is a reason for this. When I was puting all the backups in one directory it was working.

 

[bob fitz]

Try:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", "c:\GPandDetectionDogTrainingLogBackUp\Training\'" & Forms![frm_Profile]![DogName]& "'\GPandDetectionDogTrainingLogBackUpTraining.xls", True

 

[ddrew]

Well I just noticed one error, I had the wrong path, but Im still getting the same error! Code should be:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", "c:\GPandDetectionDogTrainingLogBackUp\Training\" & strBackUpDogNameFolder & "\GPandDetectionDogTrainingLogBackUpTraining.xls", True

 

[bob fitz]

Try:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", "c:\GPandDetectionDogTrainingLogBackUp\Training\'" & strBackUpDogNameFolder & "'\GPandDetectionDogTrainingLogBackUpTraining.xls", True

 

[ddrew]

Try:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", "c:\GPandDetectionDogTrainingLogBackUp\Training\'" & strBackUpDogNameFolder & "'\GPandDetectionDogTrainingLogBackUpTraining.xls", True

Same runtime error

 

[bob fitz]

Put this line of code:
Debug.Print "c:\GPandDetectionDogTrainingLogBackUp\Training\'" & strBackUpDogNameFolder & "'\GPandDetectionDogTrainingLogBackUpTraining.xls"
immediately before the DoCmd.TransferSpreadsheet line of code that is throwing the error. When the code breaks, open the immediate window and post back with it's contents.

 

[ddrew]

Put this line of code:
Debug.Print "c:\GPandDetectionDogTrainingLogBackUp\Training\'" & strBackUpDogNameFolder & "'\GPandDetectionDogTrainingLogBackUpTraining.xls"
immediately before the DoCmd.TransferSpreadsheet line of code that is throwing the error. When the code breaks, open the immediate window and post back with it's contents.

Sorry for the delay
Here is the code from the Immediate Window

c:\GPandDetectionDogTrainingLogBackUp\Trainin c:\GPandDetectionDogTrainingLogBackUp\Training\'c:\GPandDetectionDogTrainingLogBackUp\Training\Chester'\GPandDetectionDogTrainingLogBackUpTraining. xls
g\'c:\GPandDetectionDogTrainingLogBackUp\Training\Chester'\GPandDetectionDogTrainingLogBackUpTraining. xls

 

[bob fitz]

Perhaps you can see the problem now. The string used is invalid. What result do you get with:
Debug.Print strBackUpDogNameFolder

 

[ddrew]

I get this:

c:\GPandDetectionDogTrainingLogBackUp\Training\Chester

I'm not sure why its invalid though.

 

[bob fitz]

Perhaps this will work:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_DetectionTraining", strBackUpDogNameFolder & "\GPandDetectionDogTrainingLogBackUpTraining.xls", True

 

[ddrew]

Nearly, its created the file, but now errors on the line:

FileCopy sSource, sDest

Run-time error '52'
Bad file name or number

The Immediate Windo shows this:

c:\GPandDetectionDogTrainingLogBackUp\Training\Chester

 

[bob fitz]

Can you post a copy Of the db in A2003 mdb format

 

[ddrew]

Figured it out, it was the two strings sDest and sSource, tey wernt quite the same. Many thanks for your time.