View original (French)

A source file call issue

Solved
Moldude Posted messages 19 Status Membre -  
f894009 Posted messages 17417 Registration date   Status Membre 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 réponse

Réponse 1 / 1
f894009 Posted messages 17417 Registration date   Status Membre Last intervention   1 717
 
Hello,

To see:

https://grenier.self-access.com/access/fichiers/boite-de-dialogue-fichierouvrir/
1
Moldude Posted messages 19 Status Membre
 
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
f894009 Posted messages 17417 Registration date   Status Membre 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