LinkBack URL
About LinkBacks
dutchmaste 
I am trying the following:
If cell J2 has value/text: test
Workbooks.Open FileName:="O:\Splitsen\" & Range("J2") & ".xlsx"
and I run the macro it states "O:\Splitsen\.xlsx not found". While it should open workbook O:\Splitsen\test.xlsx
I am trying the following:
If cell J2 has value/text: testWorkbooks.Open FileName:="O:\Splitsen\" & Range("J2") & ".xlsx"
and I run the macro it states "O:\Splitsen\.xlsx not found". While it should open workbook O:\Splitsen\test.xlsx
Replace this line
WithWorkbooks.Open FileName:="O:\Splitsen\" & Range("J2") & ".xlsx"
With Range("J2") If .Value <> "" Then Workbooks.Open Filename:="O:\Splitsen\" & .Value & ".xlsx" End With
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Is the word "test", without quotes, indeed in cell J2?
Your code works for me but if cell J2 in the ActiveSheet (visible sheet when you run the macro) is empty I get the warning you mentioned
On which sheet does J2 hold the filename?
If posting code please use code tags, see here.
Complete script is:
The two lines in red have the text "TEST" stated but I want to change those to the text selected in cell J2.Sub IMPORTEXAMPLE() ' ' IMPORTEXAMPLE Macro ' ' Sheets("DATA").Select Workbooks.Open FileName:="O:\Splitsen\TEST.xlsx" Cells.Select Selection.Copy Windows("SPLITSEN_v1.0g.xlsm").Activate Cells.Select ActiveSheet.Paste Sheets("HS lijst").Select Range("C4").Select Windows("TEST.xlsx").Activate Application.CutCopyMode = False ActiveWindow.Close Application.CutCopyMode = True Windows("SPLITSEN_v1.0g.xlsm").Activate End Sub
@Norie sheet name and file name are is marked blue above.
Last edited by dutchmaste; 06-16-2016 at 08:10 PM.
That code is kind of like different than the original code you posted.
At time of opening this thread I thought it was just a problem with this line. But the questions made me think maybe it is something somewhere else in the script.
Only difference is between first code and the line in the complete code is:
Workbooks.Open FileName:="O:\Splitsen\" & Range("J2") & ".xlsx"Workbooks.Open FileName:="O:\Splitsen\TEST.xlsx"
OK but the Range("J2") with TEST in it is it on Sheet Data or HS Lijst ? And which Workbook ?
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
J2 is located in workbook "SPLITSEN_v1.0g.xlsm" and on worksheet "HS lijst"
So something like this should work.
And do you really need to copy an entire worksheet ?Sub IMPORTEXAMPLE() ' ' IMPORTEXAMPLE Macro ' ' fName = Workbooks("SPLITSEN_v1.0g.xlsm").Sheets("HS Lijst").Range("J2") Sheets("DATA").Select Workbooks.Open Filename:="O:\Splitsen\" & fName & ".xlsx" Cells.Select Selection.Copy Windows("SPLITSEN_v1.0g.xlsm").Activate Cells.Select ActiveSheet.Paste Sheets("HS lijst").Select Range("C4").Select Windows(fName & ".xlsx").Activate Application.CutCopyMode = False ActiveWindow.Close Application.CutCopyMode = True Windows("SPLITSEN_v1.0g.xlsm").Activate End Sub
Works like a charm, thank you so much. No more painkillers for my headache.
You're welcome and thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks