Adding one line in macro

filipivanovic

Board Regular
Joined
Oct 25, 2013
Messages
54
Greetings,

I have code:
Code:
Sub GoTo_Material()Path = "C:\Users\rsivafil\Documents\MRP Obuka\MRP01\"
Row = ActiveCell.Row
mat_index = Range("a" & Row).Value
mat_name = Range("b" & Row).Value
file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xls"
On Error GoTo kupa


Workbooks(file_name).Activate
Exit Sub
kupa:
Workbooks.Open (file_name)


End Sub


I want to insert one more row but code still need to do same thing :D

file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xls"
OR (this below is inserted line)
file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xlsx"

Is that possible?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
how about this one. (its not tested)

Code:
Sub GoTo_Material()Path = "C:\Users\rsivafil\Documents\MRP Obuka\MRP01\"

Row = ActiveCell.Row
mat_index = Range("a" & Row).Value
mat_name = Range("b" & Row).Value
file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xls"

On Error GoTo kupa

Workbooks(file_name).Activate

Exit Sub
kupa:
On Error GoTo kupa2
Workbooks.Open (file_name)

exit sub

kupa2:
On Error GoTo kupa3
file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xlsx"

Workbooks(file_name).Activate
exit sub

kupa3:
on error goto Errorh
Workbooks.Open (file_name)

exit sub
Errorh:
msgbox "Can't seem to find this file"

End Sub


You can add another Exit sub, you'll most likely find out yourself =)
 
Last edited:

filipivanovic

Board Regular
Joined
Oct 25, 2013
Messages
54
Hi Arithos, thank for quick reply, but it doesn't work.

It marks me row "kupa2", dont know why. But i tried to delete row "On error Resume next" but it also stuck on "kupa2" :'(
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
Hi Arithos, thank for quick reply, but it doesn't work.

It marks me row "kupa2", dont know why. But i tried to delete row "On error Resume next" but it also stuck on "kupa2" :'(

I edited it, I forgot kupa2: ":" is needed

I was abit quick :)


Changed much of it actually, sorry for that
 

filipivanovic

Board Regular
Joined
Oct 25, 2013
Messages
54

ADVERTISEMENT

Code:
Sub GoTo_Material()
Path = "C:\Users\rsivafil\Documents\MRP Obuka\MRP01\"


Row = ActiveCell.Row
mat_index = Range("a" & Row).Value
mat_name = Range("b" & Row).Value
file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xls"


On Error GoTo kupa


Workbooks(file_name).Activate


Exit Sub
kupa:
On Error GoTo kupa2
[COLOR=#ff0000]Workbooks.Open (file_name)[/COLOR]


Exit Sub


kupa2:
On Error GoTo kupa3
file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xlsx"


Workbooks(file_name).Activate
Exit Sub


kupa3:
On Error GoTo Errorh
Workbooks.Open (file_name)


Exit Sub
Errorh:
MsgBox "Can't seem to find this file"


End Sub

It get stuck in row i marked with red letters. Is there a way to try to find this line:
"file_name = Path & "MRP_" & mat_index & "_" & mat_name & ".xlsx""
before this line "Workbooks.Open (file_name)"
?
In the first place that was the reason why i tried to find solution for my code. because i get problem on this one: "Workbooks.Open (file_name)" but the problem vanish when i rename file from ".xls" to ".xlsx" but than i get new problem i cant open files that have extension .xls :)
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
if the error term "On Error GoTo kupa2" exist, and

kupa2: is correctly typed, it should not get stuck
 

Arithos

Well-known Member
Joined
Aug 14, 2014
Messages
598
I copied you macro to my workbook, and it does stuck on "Workbooks.Open (file_name)" :(


Hi, appologies for the late reply.

It should not get stuck on that line, since we defined the next error "On Error GoTo kupa2"

Does it simply stop? Or does it give you an error, and if it does which one?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,471
Members
409,884
Latest member
Msinmath
Top