Macro to update file with condition.

Hello Guys.

I'm a newbie in vba scripts and I would your help to find a solution for this case.

1 - My macro open a file named BancoRM.xlsx (this file do a consult in SQL in a data server and copy the data).
My first point is: how my macro can open the file BancoRM.xlsx and automatic update the information?

2 - I think this part is more complex. When i set the macro to import the data range (B5:CJ30000) from BancoRM.xlsx to Pasta2.xlsm i need create a condition to filter only the salaries listed for Rio de Janeiro (state) in the column "E". Any ideia how i can do that?

Please check my code below.

Thank you for all support. =)

Sub Copiar_Dados()
 Dim wsOrigem As Worksheet
 Dim wsDestino As Worksheet
 Dim wbk As Workbook
 Dim passwd As String
 
 passwd = "teste"

 'File Destinations, open first
 'Set the path of the same

 caminhocompleto = "C:\Users\scsbendeh\Documents\RM\BancoRM.xlsx"

 'as the worksheet is protected by password necessary to open the file before you select.
 'Opening the file as read / write, open as read-only gives error.
 Set wbk = Workbooks.Open(Filename:=caminhocompleto, ReadOnly:=False, Password:=passwd)
 'hides the file so that the user does not see the same.
 wbk.Windows(1).Visible = False

 'Files and Tabs Origin and Destination
 Set wsOrigem = Workbooks("BancoRM.xlsx").Worksheets("DADOSBD")
 Set wsDestino = Workbooks("Pasta2.xlsm").Worksheets("plan1")

 With wsOrigem
 wsDestino.Range("B5:CJ30000").ClearContents
 .Range("a2:CI30000").Copy Destination:=wsDestino.Range("B5:CJ30000")

 'Close the Destination File and Save
 'Workbooks("BancoRM.xlsx").Close SaveChanges:=False

End With
     
  
  'Fecha o Arquivo Destino e Salva
 wbk.Close SaveChanges:=False

 Call FormatarIntervalo
 

End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you don't have many posts, even though you have been a member for almost 5 years. --6StringJazzer

To answer your question 1. You should be able to go to Data -> Connections and select the connection to the database. If you then click on properties, one of them is to refresh on workbook open.

As for the other question, you will have to filter and then copy the filtered data. If you do a web search on VBA Copy Filtered Data, you will see some examples. Or you can include a sample file here (without sensitive data - change names and account numbers) and we can help you out.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.