Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FileCopy from Access (Access 2k)

    I am trying to use File Copy of contents in 1 folder to a newly created folder on my hardrive. I need to create this on the fly every month I need to copy the previous months folder and its contents to create a file for the current month. What am I doing wrong.

    This is what I have so far.

    Public Function ProviderEncounterProfile()
    On Error GoTo ProviderEncounterProfile_Error
    Dim pFile As String
    Dim pFolder As String
    Dim pTab As String
    Dim SourceFile, DestinationFile
    Dim myXl As Object, myBk As Object, myRng As Object
    Dim rs As ADODB.Recordset
    Dim sql As String, myVar As String, myCnt As Long
    Dim Ptempfile As String

    Set dbs = CurrentDb()

    strsql = "Select * from qryProviderEncountersTotals"
    Set rst = dbs.OpenRecordset(strsql)

    ' If there are no options for this Switchboard Page,
    ' display a message. Otherwise, fill the page with the items.
    If (rst.EOF) Then
    Exit Function
    Else
    While (Not (rst.EOF))
    'Create a folder based on the pFolder variable.
    'Dir() to check if the folder already exists or not.
    Ptempfile = Format(month(gTempDate) & " " & Year(gbegdate), "mmm yyyy")
    pFile = Format(month(gbegdate) & " " & Year(gbegdate), "mmm yyyy")
    pFolder = Format(month(gbegdate) & " " & Year(gbegdate), "mmm yyyy")
    pTab = gClinicName
    rst.MoveNext
    Wend
    End If
    If Dir("admfs01shareProvider Profile" & pFolder) <> "" Then
    GoTo cont:
    Else
    MkDir ("admfs01shareProvider Profile" & pFolder)
    GoTo cont:
    End If
    cont:

    SourceFile = ("admfs01shareProvider Profile" & Ptempfile & "" & Ptempfile)
    ' Define source file name.
    DestinationFile = ("admfs01shareProvider Profile" & pFolder) ' & "" & pFile)
    ' Define target file name.
    Debug.Print SourceFile
    Debug.Print DestinationFile
    FileCopy SourceFile, DestinationFile ' Copy source to target.

    DoCmd.TransferSpreadsheet acExport, 8, "qryProviderEncountersTotals", _
    "admfs01shareProvider Profile" & pFolder & "" & pFile, True ', "" & pTab, True
    'Create an Excel Instance
    Set myXl = CreateObject("Excel.Application")
    'Set your Excel File to push the data too
    Set myBk = myXl.Workbooks.Open("admfs01shareProvider Profile" & pFolder & "" & pFile) '("H:I.S. DBAbollivierDataIFHCIFHC CDBGCDBG Report 2003.xls")

    With myBk.Sheets(1)
    'Set your range to first available cell in Column A
    Set myRng = .[a65536].End(3)(2)
    'Stack an Sql Variable
    myVar = "qryProviderEncountersTotals"
    'Stack your Sql in a String
    sql = "Select * From qryProviderEncountersTotals"
    Set rs = New ADODB.Recordset
    With rs
    .ActiveConnection = CodeProject.Connection
    .Source = sql
    .Open , , adOpenStatic, adLockOptimistic
    End With
    If rs.RecordCount > 0 Then
    rs.MoveLast:
    rs.MoveFirst
    myCnt = rs.RecordCount
    'Rezise Excel Target Range Cell Array to
    'Accomodate Recordset Height/Width
    .Range(myRng, .Cells(myCnt + myRng.Row, _
    3)).CopyFromRecordset rs
    End If
    rs.Close
    Set rs = Nothing:
    Set myRng = Nothing
    End With


    'Close workbook, saving it, release WB object variable
    'myBk.Close True:
    'Set myBk = Nothing
    'Close Excel instance, release application object variable
    ' myXl.Quit:
    ' Set myXl = Nothing



    On Error GoTo 0
    Exit Function

    ProviderEncounterProfile_Error:

    MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure ProviderEncounterProfile of Module modSqlCode"
    End Function

    Any suggestions will be greatly appreciated.

    Karen

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileCopy from Access (Access 2k)

    I don't understand what your code tries to do, but there are a few things that caught my eye:

    - You declare a variable rs as an ADO recordset, but you don't use it.
    - You have a variable rst that is not declared in the code as posted, and that is treated as a DAO recordset. The variable dbs is not declared here either.

    The variables dbs and rst may be declared elsewhere in your code, but I suspect that you don't require variables to be declared explicitly. That is very dangerous, it can lead to unexpected and hard-to-trace errors. Select Tools | Options... (in the Visual Basic Editor), and make sure that the check box labeled Require Variable Declaration is ticked. This may cause irritation in the beginning, because VBA refuses to execute code if there are undeclared variables, but it will save you a lot of grief in the long term.

    You should have a reference to the Microsoft DAO 3.6 Object Library (in Tools | References...), and explicit declarations

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset ' instead of rs

    By the way, you asked "What am I doing wrong", but you haven't told us what goes wrong...

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy from Access (Access 2k)

    Sorry that I wasn't clear - the file copy can't find the file to be copied to. - File not found.

    I tried the desitnation file as follows:

    DestinationFile = ("admfs01shareProvider Profile" & pFolder)

    and

    DestinationFile = ("admfs01shareProvider Profile" & pFolder & "" & pFile)

    Neither one seems to work.

    SourceFile = ("admfs01shareProvider Profile" & Ptempfile & "" & Ptempfile)
    ' Define source file name.
    DestinationFile = ("admfs01shareProvider Profile" & pFolder) ' & "" & pFile)
    ' Define target file name.
    Debug.Print SourceFile
    Debug.Print DestinationFile
    FileCopy SourceFile, DestinationFile ' Copy source to target.

    I need to create a folder on a specific drive and within that folder copy the contents of the previous month's folder and then rename the file. Is there a better way of doing this?

    Thanks.

    Karen

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileCopy from Access (Access 2k)

    You already have Debug.Print instructions in your code. What do the results tell you? You can see what the SourceFile and DestinationFile are, perhaps you will discover a discrepancy.

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy from Access (Access 2k)

    Debug retuns the following:
    admfs01shareProvider ProfileFeb 2004Feb 2004
    admfs01shareProvider ProfileMar 2004

    Or with the file name in the Destination I get

    admfs01shareProvider ProfileFeb 2004Feb 2004
    admfs01shareProvider ProfileMar 2004Mar 2004

    I verify that the Mar 2004 folder is created but the Mar 2004 file has not be copied.

    Karen

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileCopy from Access (Access 2k)

    If you are allowed to use scripting (the network admins in some corporate networks disable it), you can use the FileSystemObject. It has a CopyFolder method that will copy an entire folder with its contents, and also a MoveFolder method.

    To use scripting, set a reference in Tools | References... to Microsoft Scripting Runtime.

  7. #7
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy from Access (Access 2k)

    Can you direct me in the right direction - do you have any sample code?

    Karen

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileCopy from Access (Access 2k)

    The official Microsoft documentation is here.

    If you do a search (across all forums) for CopyFolder, you'll find a few code samples, for instance in <post#=98749>post 98749</post#>.

  9. #9
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy from Access (Access 2k)

    If I do not have the capablity for scripting what are my other options?

    Karen

  10. #10
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy from Access (Access 2k)

    I tried to use CopyFolder as you suggested and I get (error 424 (object required)in procedure ProviderEncounterProfile of Module modSqlCode) see attachement- what do I need to do to fix this.

    SourceFile = ("admfs01shareProvider Profile" & Ptempfile & "" & Ptempfile)
    ' Define source file name.
    DestinationFile = ("admfs01shareProvider Profile" & pFolder & "" & pFile)
    ' Define target file name.

    FileSystemObject.CopyFolder "admfs01shareProvider Profile" & Ptempfile & "" & Ptempfile, "admfs01shareProvider Profile" & pFolder & "" & pFile
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileCopy from Access (Access 2k)

    Something with FileCopy should work, but I don't have the slightest idea what all that code you posted does or is supposed to do. Sorry.

  12. #12
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy from Access (Access 2k)

    All I am trying to do is copy a file from 1 directory to another and then rename the folder and file contained within.

    ie. copy file Feb 2004Feb 2004 and create a new folder = Mar 2004Mar 2004

    Could you please use the above info and give me a correct syntax for coping of a file - if possible a sample of code that does not use the FilesystemObecjt string and 1 with the filesystemobject. I tried to use the samples and I am still getting the above mentioned error. Hopefully you may have a better solution.

    thanks,

    Karen

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileCopy from Access (Access 2k)

    I still don't understand. What are the file names and what are the folder names? Why doesn't the file name have an extension? Rename from what to what? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  14. #14
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileCopy from Access (Access 2k)

    File 1 = S:Provider ProfileFeb 2004Feb 2004.xls
    File 2 = S:Provider ProfileMar 2004 and copy Feb 2004.xls to Mar 2004.xls

    copy File 1 to File 2

    Hope this helps.

    Karen

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileCopy from Access (Access 2k)

    ?? File 2 = S:Provider ProfileMar 2004 ??

    S:Provider ProfileMar 2004 looks like a folder name, not a file name.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •