VBA if i cancel the update it clears the sheet

[malveiro]

Hello

I've have a userform , and when i click on button to update a sheet , execute the module above and it's working ,it opens a file explorer to choose the file i select the file , it clears the sheet , and it updates the values.
The problem is , if i cancel the update in the file explorer , it gives me the MsgBox " Actualização cancelada" but also clears the sheet
How can i prevent , if i cancel , to clear the sheet ?
I want is , if i cancel , to not update e sheet1 remains the same before clicking the button
Thank you in advance

Sub copy_worksheet()

On Error GoTo Cancel

Application.ScreenUpdating = False

ThisWorkbook.Sheets("Sheet1").Range("A2:D5000").Clear

Set Source_workbook = Workbooks.Open(Application.GetOpenFilename(FileFilter:="Excel Files,*.xls*"))

Source_workbook.Sheets("Sheet1").Range("A2:D5000").Copy _
ThisWorkbook.Sheets("Sheet1").Range("A2:D5000")

Source_workbook.Close SaveChanges:=False

Application.ScreenUpdating = True

MsgBox "Lista de Impressoras actualizada "
Exit Sub
If Err.Number > 0 Then GoTo Cancel
Cancel:

MsgBox " Actualização cancelada"

End Sub

 

[JEC]

Try this one. The first step in your code is deleting that range.

Sub jec()
 Dim ar
 With Application.FileDialog(3)
   .Filters.Clear
   .Filters.Add "Excel Files", "*.xlsx; *.xlsm"
   If .Show Then
      With Workbooks.Open(.SelectedItems(1))
        ar = .Sheets("Sheet1").Range("A2:D5000")
        ThisWorkbook.Sheets("Sheet1").Range("A2").Resize(UBound(ar), UBound(ar, 2)) = ar
       .Close 0
      End With
   End If
 End With
End Sub

 

[malveiro]

Try this one. The first step in your code is deleting that range.

Sub jec()
 Dim ar
 With Application.FileDialog(3)
   .Filters.Clear
   .Filters.Add "Excel Files", "*.xlsx; *.xlsm"
   If .Show Then
      With Workbooks.Open(.SelectedItems(1))
        ar = .Sheets("Sheet1").Range("A2:D5000")
        ThisWorkbook.Sheets("Sheet1").Range("A2").Resize(UBound(ar), UBound(ar, 2)) = ar
       .Close 0
      End With
   End If
 End With
End Sub

It worked , Thank you so much @JEC .
Is it better to use Application.Filedialog instead of Workbooks.Open(Application.GetOpenFilename) ? What's the difference between them ??

Once again , Thank you so much

 

[JEC]

The same but I like it more. It is simpler
Cheers

 

[malveiro]

The same but I like it more. It is simpler
Cheers

Thank you , Just one last question : Is it possible , with vba , to get data from an Embedded Web Server ? I've tried several ways to get toner level from printers Lexmark , which appear to be Embedded Web Server . My code is able to get from printers that aren't Embedded Web Server ,but from the ones that are , i can't . I've got an post here , but never got an answer

Once again Thank you so much

 

[JEC]

Hard to tell. Getting printer details and settingd is never an easy job.

 

[malveiro]

Hard to tell. Getting printer details and settingd is never an easy job.

Well i managed to lexmark printers , to get the toner values , except the ones who are embbeded web server .
Once again , Thank you for your help
Best regards

 

[malveiro]

@JEC , sorry to bother you , in the code that you sent and it's working , how can i extract the filename from the file ?
Example , when the Application.FileDialog opens and i choose the excel file , like 16_01_2024.xlsx , i want , after the update , to insert the 16_01_2024 in a Label ?
I tried to define a file as variant , set the file as selected item , with no success
Thank you

Best regards

 

[malveiro]

@JEC , i just find it out , now i'm struggling to insert that value in memory , so when i start the userform , it gives me that value

 

[JEC]

You can put it in memory but you can also put the name in a cell. Then refer to that cell in your userform_initialize.
The name will be put in the cell right to the imported data.

Sub jec()
 Dim ar, fn
 Application.ScreenUpdating = False
 With Application.FileDialog(3)
   .Filters.Clear
   .Filters.Add "Excel Files", "*.xlsx; *.xlsm"
   If .Show Then
      fn = Split(Dir(.SelectedItems(1)), ".")(0)
      With Workbooks.Open(.SelectedItems(1))
         ar = .Sheets("Sheet1").Range("A2:D5000")
         With ThisWorkbook.Sheets("Sheet1").Range("A2")
            .Resize(UBound(ar), UBound(ar, 2)) = ar
            .Offset(-1, UBound(ar, 2) + 1) = fn
         End With
        .Close 0
      End With
   End If
 End With
End Sub