VBA to open the saved workbook and update the data

[hanim554]

Dear Friends,
I stuck with some thing, cant go ahead, any one please look into.
I have data in B5:J3000 in sheet 'Master' of workbook 'Master Data', i have workbooks already created in the C Drive with name mentioned in J5:J3000. i would like to have a code which will filter the data based on J5:J3000, open existing workbook with name mentioned J5:J3000, update the data and close the file.

two files are attached for example. file Master Data is master file running the code, then it need to open the file mentioned in J5:J3000 (file 'Master Data1 is one of the name in J5:J3000) and update the Data.
Please note file mentioned in J5:J3000 is created to login with userid and pw. it is as below.
user name: admin
pw : admin99.

Regards
Hanim

 

[Smallman]

Hanim

Why dont' you just put all of the data you want to update (Master File 1-5) in the same folder then just iterate through the files in that folder add add the information.

Option Explicit
Sub OpenImp()
    Const sPath = "D:\Smallman\"
    Dim sFil As String
    Dim owb As Workbook
    Dim sh As Worksheet
   
    sFil = Dir(sPath & "*.xl*")
    Set sh = Sheet1 'Sheet code name of Master Sheet
 
    Do While sFil <> ""
        Set owb = Workbooks.Open(sPath & sFil)
        Sheets("Master").Range("B4:F3000").Copy Sheet3.Range("B" & Rows.Count).End(xlUp)(2)
        owb.Close False 'Save on Close
        sFil = Dir
    Loop
End Sub

You should be able to adapt this to your needs even if it is not exactly what you want.


You might also be interested the 800 line tomb you have used to service your userform login with can be done with the following;

Private Sub CommandButton1_Click()
Dim username As String
Dim password As String
Dim mySheet As String
Dim temp1 As String
Dim temp2 As String
Dim i As Integer
 
username = TextBox1.Text
password = TextBox2.Text
temp1 = Sheet30.[d3]
temp2 = Sheet30.[e3]
 
    If username = temp1 And password = temp2 Then
        Sheet1.Visible = True
        Sheet30.Visible = True
        Exit Sub
    Else
        For i = 4 To 124 'or however many lines you want
            temp1 = Sheet30.Range("D" & i) 'Users tab
            temp2 = Sheet30.Range("E" & i) 'Pword Check
          If username = temp1 And password = temp2 Then GoTo Valid
        Next i
    End If
 
 'Only for invalid
    MsgBox "incorrect id and pw", vbCritical 'Code for invalid
    ActiveWorkbook.Close
Valid: 'code for Valid
    MsgBox "logined successfully", vbInformation
    Unload Me
    Sheet1.Visible = True
    Sheet1.Activate
End Sub

In your original coding D57 is mentioned twice and your numbering goes from 4 to 124 then starts at 225.
The above should run more smoothly and once again you should be able to adapt.

Take care

Smallman

 

[hanim554]

Dear Smallman,
thanks for your time. you are a bigman in your work even if you name is smallman
i created 5 files Master Data1,Master Data2... till five in same folder. when i am running the code, it is not getting copied to these five files. i tried to modify the code, still it is not taking me to required result. thanks in advance for your help.
Regards
Hanim

 

[Smallman]

Hi Hanim

My last name is Small hence the handle of Smallman. Thanks for the compliment. I see a mistake in my coding. I am not sure but I have set Sh = Sheet1

Then when I go to copy I am copying to sheet3. Instead of Sheet3 put

Sh

In theory this should solve your problem if it does not then just come back on the forum and I will upload a sample which does work.

Take care

Smallman

 

[hanim554]

Hi,
still it is not working, giving me error message "microsoft excel encountered a problem and need to close".
Regards
Hanim

 

[Smallman]

What line gives you and error message. Have you ensured that your file path is correct and that you have a back slash after your path like this

Const sPath = "D:\Smallman\"

My coding works on my computer so can you post the code you are using. Spotting the error should not be too much trouble.

Ta

Smallman

 

[hanim554]

Hi,
I had already changed path address. please find the code below. hope i conveyed my question properly. running the code in the File Master Data
and it is to filter data by J4:J3000 with file name Master Data1,Master Data2 mentioned there and copy B4:J3000 and paste in the files already crea-
ted in the same folder with same name mentioned in the J4:J3000. appreciate you help.

Sub OpenImp()
    Const sPath = "C:\Workshop\"
    Dim sFil As String
    Dim owb As Workbook
    Dim sh As Worksheet
   
    sFil = Dir(sPath & "*.xl*")
    Set sh = Sheet1 'Sheet code name of Master Sheet

    Do While sFil <> ""
        Set owb = Workbooks.Open(sPath & sFil)
        Sheets("Master").Range("B4:J3000").Copy Sheet1.Range("J" & Rows.Count).End(xlUp)(2)
        owb.Close False 'Save on Close
       sFil = Dir
    Loop
End Sub

Regards
Hanim

 

[Smallman]

Let me create a folder called C workshop I will run some tests and post back the results. We will get you over the line

 

[Smallman]

Hi Hanim

I just ran the procedure on my machine. I put two files in the folder with 15 rows of data in each. The files both opened and the data was imported beautifully. 30 Rows added to Master Data 1 with No errors.

You might want to have a look at where the data is pasting to though. You have Col J and I think you need to have it hit Column B.

Take care

Smallman

 

[hanim554]

Hi Smallman,

Thank you so much. i will wait for that.

Reagards
Hanim

 

[Smallman]

Hanim

You misunderstand. I am telling you I ran the file at my end and it runs well. So the problem is at your end. It won't help if I post a file the code will be the same other than the alteration I suggested in my last post.

Take care

Smallman