Hardcoding the path of the file in macro(macro Correction)

[zyxw1234]

Hi Experts,

Code: Select all

Sub STEP7()
    On Error Resume Next
    Application.ScreenUpdating = False

    aps = Application.PathSeparator   'i have to keep this line or not
    Wb = ThisWorkbook.Path
    wb0 = ThisWorkbook.Name
    Wb1 = "ap.xls"
    Wb2 = "PL.xlsx"

    Workbooks.Open (Wb & aps & Wb1)
    Wb1 = ActiveWorkbook.Name
    If Err.Number  0 Then
        MsgBox Err.Description
        Exit Sub
    End If

    Workbooks.Open (Wb & aps & Wb2)
    Wb2 = ActiveWorkbook.Name
    If Err.Number  0 Then
        MsgBox Err.Description
        Exit Sub
    End If

    ALL_SAME = True
    e = 2
    Do
        chk_e = Workbooks(Wb1).Sheets(1).Cells(e, "E")
        chk_y = Workbooks(Wb1).Sheets(1).Cells(e, "Y")
        a = WorksheetFunction.Match(chk_e, Workbooks(Wb2).Sheets(1).Range("A:A"), 0)
        If Err.Number = 0 Then
            With Workbooks(Wb2).Sheets(1)
                x = .Cells(a, .Columns.Count).End(xlToLeft).Column + 1
                If x < 3 Then x = 3
                .Cells(a, x) = chk_y
                If .Cells(a, x)  .Cells(a, x - 1) Then ALL_SAME = False
            End With
            bg = xlNone
        Else
            bg = 6
            Err.Clear
        End If
        Workbooks(Wb1).Sheets(1).Cells(e, "E").Interior.ColorIndex = 0
        e = e + 1
    Loop Until Workbooks(Wb1).Sheets(1).Cells(e, "E") = Empty
    Workbooks(Wb1).Close True
    Workbooks(Wb2).Close True


End Sub

I need to mention the path of all the files & May i know what changes are requierd in this macro?

[HansV]

You have seen many examples of what you need here on Eileen's Lounge and on other boards, so you should be able to do this yourself.

[zyxw1234]

Code: Select all

Sub STEP7()
    Dim Wb1 As Workbook
    Dim Wb2 As Workbook
    Dim Ws1 As Worksheet
    Dim Ws2 As Worksheet
    Set Ws1 = Wb1.Worksheets(1)
    Set Ws2 = Wb2.Worksheets(1)
    On Error Resume Next
    Application.ScreenUpdating = False

    aps = Application.PathSeparator   'Plz confirm i have to keep this line or not?
 
    Set Wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\ap.xls")
    Wb1 = ActiveWorkbook.Name
    If Err.Number  0 Then
        MsgBox Err.Description
        Exit Sub
    End If

    Set Wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\PL.xlsx")
    Wb2 = ActiveWorkbook.Name
    If Err.Number  0 Then
        MsgBox Err.Description
        Exit Sub
    End If

    ALL_SAME = True
    e = 2
    Do
        chk_e = Ws1.Cells(e, "E")
        chk_y = Ws2.Cells(e, "Y")
        a = WorksheetFunction.Match(chk_e, Ws2.Range("A:A"), 0)
        If Err.Number = 0 Then
            With Ws2
                x = .Cells(a, .Columns.Count).End(xlToLeft).Column + 1
                If x < 3 Then x = 3
                .Cells(a, x) = chk_y
                If .Cells(a, x)  .Cells(a, x - 1) Then ALL_SAME = False
            End With
            bg = xlNone
        Else
            bg = 6
            Err.Clear
        End If
        Ws1.Cells(e, "E").Interior.ColorIndex = 0
        e = e + 1
    Loop Until Ws1.Cells(e, "E") = Empty
    Workbooks(Wb1).Close True
    Workbooks(Wb2).Close True


End Sub

Plz see i have made the changes & i have 1 doubts also plz see the remarks in the macro & that is my doubt also

[HansV]

1) You must move the lines

Code: Select all

    Set Ws1 = Wb1.Worksheets(1)
    Set Ws2 = Wb2.Worksheets(1)

to BELOW the lines where you open Wb1 and Wb2.

2) Remove the lines

Code: Select all

    Wb1 = ActiveWorkbook.Name

and

Code: Select all

    Wb2 = ActiveWorkbook.Name

Wb1 and Wb2 are workbook objects, not workbook names.

3) Change both instances of

Code: Select all

    If Err.Number = 0 Then

to

Code: Select all

    If Err.Number <> 0 Then

If an error occurs, the error number is different from 0. If it equals 0, no error occurred.

4) You don't use

Code: Select all

    aps = Application.PathSeparator

so you can safely delete that line.

5) Change

Code: Select all

    Workbooks(Wb1).Close True
    Workbooks(Wb2).Close True

to

Code: Select all

    Wb1.Close True
    Wb2.Close True

See 2)

6) You assign a value to the variables ALL_SAME and bg but you don't do anything with them.

[StuartR]

4) You don't use

Code: Select all

    aps = Application.PathSeparator

so you can safely delete that line.

I think it is used in the line

Code: Select all

Workbooks.Open (Wb & aps & Wb1)

[zyxw1234]

Ok HansV Sir
Thnx Alot for helping me in modifying this macro
Have a Awesome Day Sir

[HansV]

I think it is used in the line

Code: Select all

Workbooks.Open (Wb & aps & Wb1)

Hi Stuart, that was in the earlier version of the macro, not in the revised version.