View original (French)

A source file call issue

Solved
Moldude Posted messages 19 Status Member -  
f894009 Posted messages 17417 Registration date   Status Member Last intervention   -
Hello,
I would like to improve my cell copy macro from one file to another by including a dialog box to select the source file for copying. Having not found what I was looking for in various resources, I am reaching out to this forum which has already provided me with several solutions to my problems.
I would like that when the macro is launched, a dialog box opens and I can insert the name (or the path of the file) inside. This name will then be used in the macro as "Workbook1"
The goal is to be able to use this macro for several files without having to go back into the code each time to change the name of the source file.

Here is a part of my macro
Sub Macro4()
'
' Macro4 Macro
'

'
Windows("Workbook1").Activate
Sheets("Macro").Select
Range("B2").Select
Selection.Copy
Windows("Workbook2").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Thank you in advance.

1 answer

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    To see:

    https://grenier.self-access.com/access/fichiers/boite-de-dialogue-fichierouvrir/
    1
    1. Moldude Posted messages 19 Status Member
       
      Super, thanks for taking the time to respond,
      but there's a small problem, the dialog box works but I don't see how to "call it" in the rest of my macro (under what name the variable has been stored)
      Currently I have this:


      Sub SelectionFichier01()
      Dim fd As Office.FileDialog
      Set fd = Application.FileDialog(msoFileDialogOpen)
      fd.Title = "Select a file..."
      fd.AllowMultiSelect = False
      If fd.Show() Then
      MsgBox "You have selected the file: " _
      & vbCrLf & fd.SelectedItems(1), vbInformation
      End If
      Set fd = Nothing
      End Sub

      Sub Macrotestafb()
      '
      ' Macro4 Macro
      '

      '
      Call SelectionFichier01

      Windows("fd").Activate
      Sheets("Macro").Select
      Range("B2").Select
      Selection.Copy
      Windows("Workbook2").Activate
      Range("B3").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      End Sub

      I'm getting the error "The index does not belong to the selection (it can't find it if I understood correctly)
      and so I don't know under what name the imported file is stored (formerly "Workbook1" now "fd").

      Thanks in advance
      0
    2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Re,

      This code is more appropriate, but since it doesn't open the file, it won't work.
      Moreover, I don't understand what you want to do with your

      ability to use this macro for multiple files without having to go back into the code each time to change the name of the source file. 


      Sub Macrotestafb() ' File = SelectionFichier01 If File = False Then MsgBox "Warning: no file selected!!!!!!!!!" Exit Sub End If End Sub Function SelectionFichier01() Dim strFileToOpen As Variant strFileToOpen = Application.GetOpenFilename _ (Title:="Please choose a file to open", _ FileFilter:="Excel Files *.xls* (*.xls*),") SelectionFichier01 = strFileToOpen End Function
      0