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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
Upvote 0
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" :'(
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
if the error term "On Error GoTo kupa2" exist, and

kupa2: is correctly typed, it should not get stuck
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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
Back
Top