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?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,600
Messages
5,838,291
Members
430,537
Latest member
Antonio11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top