Need help - Naming the file path instead of using thisworrkbook.path. Thanks for helping!

[pedie]

Sorry I have to restart part of my old thread.
Could someone please edit this part or the whole code..

MyFile = [COLOR=red]ThisWorkbook.Path & "\Book2.xls"[/COLOR]

I want myself to enter the whole path myself like 'C\Macro and Excels trix\.. etc and not "thisworkbook.path & \book2.xls"

Thanks pedie

Sub Test2()

    Dim wb As Workbook, MyFile As String
    
    MyFile = [COLOR=red]ThisWorkbook.Path & "\Book2.xls"[/COLOR]
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile)
    On Error GoTo 0
    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
    
    With wb
        '.Activate
        '.Cells.Activate
        With ActiveSheet
            .Range("A1").Value = "Hello!"
            .Range("A3").Formula = "=A1"
        End With
        .Save
        .Close
    End With
    
End Sub

 

[VoG]

Perhaps

Set wb = Workbooks.Open(Filename:=MyFile, IgnoreReadOnlyRecommended:=True)

 

[pedie]

Hi Vog!

This is how my code exactly looks like now.
It is still giving me a pop up for read only.
Maybe I am not doing it correctly. Please go through me code. & add one more thing.
Suppose i want to copy the data from UseTracker and then go back to Book1 from where I am running the code and paste it there in sheet1. I tried it and then It paste in UseTracker.xls and not in Book1.

Thanks for again for helping!
Pedie

Sub Test2()
 Dim wb As Workbook, MyFile As String
    MyFile = "C:\Documents and Settings\pedie\UserData\My Folder\UseTracker.xls"
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile, IgnoreReadOnlyRecommended:=True)
     On Error GoTo 0
    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
    wb.Sheets("Data").Select
    Range("A2:A16").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells(1).Select
End Sub

 

[VoG]

I don't know why it would be opening as read only but as you are only reading from it we can open it as read only and you shouldn't get the prompt. See if this works

Sub Test2()
 Dim wb As Workbook, MyFile As String
    MyFile = "C:\Documents and Settings\pedie\UserData\My Folder\UseTracker.xls"
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile, ReadOnly:=True)
     On Error GoTo 0
    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
    wb.Sheets("Data").Range("A2:A16").Copy
    wb.Close
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells(1).Select
End Sub

 

[pedie]

Vog, That works perfectly. No pop ups now.

wb.Close
     'instead of ActiveSheet.Paste can i do something like 
    sheets("Sheet1").select
activesheet.range("E1").select
activesheet.paste???

 

[pedie]

VoG!, I can carry on from here for this Thread....!
Thank you so much for everything again! I dont think even if I pay someone, they wont be able to help me this far....
Thanks you is all I can say!
See you in another thread soon

Regards
Pedie

 

[VoG]

This would be better

Sub Test2()
 Dim wb As Workbook, MyFile As String
    MyFile = "C:\Documents and Settings\pedie\UserData\My Folder\UseTracker.xls"
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:=MyFile, ReadOnly:=True)
     On Error GoTo 0
    
    If wb Is Nothing Then MsgBox "Couldn't locate " & MyFile: Exit Sub
    wb.Sheets("Data").Range("A2:A16").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range("E1")
    wb.Close
    Application.CutCopyMode = False
End Sub

 

[pedie]

Yea..this one is much more better then what I did figure out!!!

Thanks again VoG!!!
Pedie