How to take data from the a unopen workbook first sheet without specifying the actual name (because it change every week)

[Gwhaou]

Hello,

For a project I actually use a macro to export data from one unopen workbook sheet to the actual xlsm workbook sheet.
I recently found a problem on the export sheet, this sheet has to be updated every single week but probel the change the name of the sheet changes every week.
But on my export code I need to specify the actual sheet name, my question is : Is there any way to export data from the selected workbook without specifying the name of the sheet (generally is the first sheet)

This is the code :

Public Sub Export()


Dim Export_Wrk As Workbook
Dim Main_Wrk  As Workbook

Dim WaySheet As String

    With Application.FileDialog(msoFileDialogFilePicker)
   
        If .Show <> 0 Then
       
            WaySheet = .SelectedItems(1)
               
            Set Export_Wrk = Workbooks.Open(WaySheet)
            Set Main_Wrk = Workbooks("Template.xlsm")

            Export_Wrk.Sheets("export_week1").Activate ' I have to specify the name of the sheet
            Last_ligne = Export_Wrk.Sheets("export_week1").Cells(Rows.Count, "A").End(xlUp).Row ' I have to specify the name of the sheet
            Main_Wrk.Sheets("export_week1").Range("A1:AG" & Last_ligne).Copy ' I have to specify the name of the sheet

   
            Main_Wrk Sheets("Export_1").Activate
            Range("A1").Select
            ActiveSheet.Paste
   

            Application.CutCopyMode = False
            Export_Wrk.Close
       
        Else
            MsgBox "You have canceled the transfert"
            Exit Sub
       
        End If
        End With
    
End Sub

I will appreciate some help
Remark : The unopen workbook is a csv file which is weekly uploaded on the database so The name of the workbook and the name of the first sheet is the same.

 

[Fluff]

As the file is a csv file it will only have one sheet, so you can use

Export_Wrk.Sheets(1)

 

[Gwhaou]

As the file is a csv file it will only have one sheet, so you can use

Export_Wrk.Sheets(1)

yes sir, it works perfectly.

I have a doubt, let's take a example of a xlsx file with more than one sheet and I want to copy data only from the first sheet or the third.
In this case

 Export_Wrk.Sheets(1)

or

 Export_Wrk.Sheets(3)

will work ?

 

[Fluff]

Yes that will work.

 

[Gwhaou]

Yes that will work.

Ok thanks

 

[Fluff]

You're welcome & thanks for the feedback.