Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12

    Button to create/open or copy/open an excel file

    Hello everyone. I am new here looking for help to my situation:

    I need a button which will be placed inside the detail section. It will create or open (if it was already created) an excel file.
    I already have a button that creates (or opens) a folder for every customer. In that folder I keep his files and this excel file.The name of the folder is taken from the first field (reference number) and it's unique.

    Here is another idea that will work too:

    I will have an excel file in a folder when when I click on the button it will copy that file to the specific folder that was created with the below (folder creation/opening) button.
    Is this code easier?

    Here is the code of the folder button to help you.

    Private Sub command81_Click()

    Const FOLDER_EXISTS = 75
    Dim strFolderPath As String
    strFolderPath = "c:\data files\praktor" & Me.[AA]

    On Error Resume Next
    MkDir "c:\data files\praktor"
    Err.Clear
    MkDir "c:\data files\praktor"
    Err.Clear
    MkDir strFolderPath
    Select Case Err.Number
    Case 0
    ' no error
    Case FOLDER_EXISTS
    ' folder exists so open it
    Application.FollowHyperlink strFolderPath
    Case Else


    ' unknown error so inform user
    MsgBox Err.Description, vbExclamation, "Error"
    End Select

    End Sub


    Thank you
    [/COLOR]

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,058
    paste the copy1File into a module, then use:

    copy1File sSrcFile, sTargFile


    Code:
    Public Sub Copy1File(ByVal pvSrc, ByVal pvTarg)
    Dim fso
    On Error GoTo errMake
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile pvSrc, pvTarg
    Set fso = Nothing
    exit sub
    
    errMake:
    MsgBox Err.Description & vbCrLf & pvDir, , "Copy1File(): " & Err
    Set fso = Nothing
    End Sub

  3. #3
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12
    Thank you ranman256 but I need some more instructions.

    The excel file will be in C:\data files\praktor\custinfo.xlsx
    and it will be copied in c:\data files\praktor\1
    c:\data files\praktor\2
    c:\data files\praktor\3 etc

    Thank you

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,058
    this stupid site keeps breaking up my code blocks...

    vXLfile = "custinfo.xlsx
    vDir = ""C:\data files\praktor"
    vSrcFile = vDir & vXLfile[/code]
    [code]
    for i = 1 to 3
    vTargDir = vDir & i & "\"
    makedir vTargDir

    copy1File vSrcFile , vTargDir & vXLfile
    next




    in case you need to make the folders:

    Code:
    Public Sub MakeDir(ByVal pvDir)
    Dim fso
    On Error resume next   'GoTo errMake
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(pvDir) Then fso.CreateFolder pvDir     'MkDir pvDir
    Set fso = Nothing
    Exit Sub
    Code:
    errMake:
    'MsgBox Err.Description & vbCrLf & pvDir, , "MakeDir(): " & Err
    Set fso = Nothing
    End Sub
    




  5. #5
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12
    I get this error

    https://www.screenpresso.com/cloud/8Szvc/
    Attached Thumbnails Attached Thumbnails 2021-10-01_15h42_05.png  

  6. #6
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12
    I get this error "Sub or function not defined" and it highlights the "makedir vDir" line

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,937
    Post 5 was moderated, I'm posting to trigger email notifications. Also:

    https://docs.microsoft.com/en-us/off...kdir-statement
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,937
    Did you read the link I posted? It's MkDir, not makedir.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    Did you read the link I posted? It's MkDir, not makedir.
    You are right, I didn't notice the syntax error.
    Now the same error with "copy1file" command!

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,937
    I'm only familiar with

    https://docs.microsoft.com/en-us/off...copy-statement

    I just noticed ranman posted a custom function named MakeDir so another option would be to copy that function into a standard module and use it instead of MkDir. They also posted a custom function named copy1file, so you have the same option there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12
    I don't know anything about modules or code.

    I am trying to make a button to copy a file to a folder or open it if it was already copied there.
    The folder is already created with another button which I will always click first.

    Here is that button's code:
    Private Sub command81_Click()

    Const FOLDER_EXISTS = 75
    Dim strFolderPath As String
    strFolderPath = "c:\data files\praktor" & Me.[AA]

    On Error Resume Next
    MkDir "c:\data files\praktor"
    Err.Clear
    MkDir "c:\data files\praktor"
    Err.Clear
    MkDir strFolderPath
    Select Case Err.Number
    Case 0
    ' no error
    Case FOLDER_EXISTS
    ' folder exists so open it
    Application.FollowHyperlink strFolderPath
    Case Else
    ' unknown error so inform user
    MsgBox Err.Description, vbExclamation, "Error"
    End Select

    End Sub

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,937
    Well, if you want to learn, here's a primer:

    http://www.baldyweb.com/Function.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    petrikos is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    Well, if you want to learn, here's a primer:

    http://www.baldyweb.com/Function.htm
    Thanks but I am not willing to learn programming just because I need a simple code for a button.
    I don't have the time either.
    So, is there someone to help me please?

  15. #15
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,155
    Quote Originally Posted by petrikos View Post
    Thanks but I am not willing to learn programming just because I need a simple code for a button.
    I don't have the time either.
    So, is there someone to help me please?
    This is a very blinkered response.
    If you aren't willing to invest some time to solve your problem, why should someone else, who is unpaid, invest time in a solution for you??
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-14-2020, 06:55 PM
  2. Workbooks.Open won't open Excel file in Edit Mode
    By jeffhanner10@gmail.com in forum Programming
    Replies: 5
    Last Post: 02-15-2020, 11:51 AM
  3. Replies: 6
    Last Post: 09-14-2017, 07:31 AM
  4. Copy Open db file
    By gg80 in forum Programming
    Replies: 4
    Last Post: 04-24-2013, 04:35 PM
  5. Switchboard button to open txt file
    By Dannat in forum Import/Export Data
    Replies: 17
    Last Post: 01-24-2012, 10:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums