[Solved] VBA chdir problem

[megs.rs]

Hi there!

I hope that anybody is well.

I made a excel worksheet to organize my mame's games.

It's work very well in excel on windows.

But, i was trying to do work on LibreOffice on Fedora and i am having problem.

This is my code:

Code: Select all

        print Worksheets("configuracao").Cells(1, 2)
        ChDir Worksheets("configuracao").Cells(1, 2)
        print curdir()
        Shell "mame.exe " + "roms/" + Application.Selection, vbNormalFocus

This code return error '53', "file not found" i think.

Debugging the code i see that chdir is not working.

Anyone can help me about this?

Thanks a lot,

Miguel Silva

[Hagar Delest]

Hi and welcome to the forum!

Basic language in MS Excel and LO are different. You need to rewrite your macro with the matching function in LO (if it exists).

[Zizi64]

Please upload your sample file and the full macro code here.

[JeJe]

Have you tried putting the full file path in you Shell statement?

[megs.rs]

Hey guys,

I think you know I'm running MAME on WINE. Works fine on my Fedora.

But MAME needs to run in its own directory.

To solve this problem, I made a workaround. I made a shell script that changes to the MAME directory and runs the game in the parameter.

Everything is fine now. But 'chdir' function didn't work for 'shell' function

Here is the complete code:

Code: Select all

Private Sub CommandButton2_Click()
    If Application.Selection.Count = 1 And Application.Selection.Row >= 8 And Application.Selection.Column = 3 Then
        CommandButton2.Visible = False
        rem print Worksheets("configuracao").Cells(1, 2)
        rem ChDir Worksheets("configuracao").Cells(1, 2)
        rem print curdir()
        rem print "mame.exe " + "roms/" + Application.Selection
        Shell "runmame.sh " + "roms/" + Application.Selection, vbNormalFocus
        Worksheets("jogos").Cells(Application.Selection.Row, Application.Selection.Column - 1) = Worksheets("jogos").Cells(Application.Selection.Row, Application.Selection.Column - 1) + 1
        Cells(Application.Selection.Row, 1).Activate
        CommandButton2.Visible = True
    Else
        MsgBox "Selecione o Jogo"
    End If
End Sub

[Bidouille]

Everything is fine now. But 'chdir' function didn't work for 'shell' function

Hmm read your code:

Code: Select all

        rem ChDir Worksheets("configuracao").Cells(1, 2)

If you set a rem, it sound logical.

[megs.rs]

I'm not that stupid...

[karolus]

I'm not that stupid...

…but ignorant enough to use »Application.Selection« 9 times in 15 lines of code!!

Code: Select all

Private Sub CommandButton2_Click()
    sel = Application.Selection
    If sel.Count = 1 And sel.Row >= 8 And sel.Column = 3 Then
        CommandButton2.Visible = False
        rem print Worksheets("configuracao").Cells(1, 2)
        rem ChDir Worksheets("configuracao").Cells(1, 2)
        rem print curdir()
        rem print "mame.exe " + "roms/" + sel
        Shell "runmame.sh " + "roms/" + sel , vbNormalFocus
        Worksheets("jogos").Cells(sel.Row, sel.Column - 1) = Worksheets("jogos").Cells(sel.Row, sel.Column - 1) + 1
        Cells( sel.Row, 1).Activate
        CommandButton2.Visible = True
    Else
        MsgBox "Selecione o Jogo"
    End If
End Sub

[JeJe]

With/end with can simplify and make code more readable, in case anyone isn't aware of it. Simplifying the last post:

Code: Select all

Private Sub CommandButton2_Click()
	sel =Application.Selection
    with sel
    If .Count = 1 And .Row >= 8 And .Column = 3 Then
        CommandButton2.Visible = False
        rem print Worksheets("configuracao").Cells(1, 2)
        rem ChDir Worksheets("configuracao").Cells(1, 2)
        rem print curdir()
        rem print "mame.exe " + "roms/" + sel
        Shell "runmame.sh " + "roms/" + sel , vbNormalFocus
        Worksheets("jogos").Cells(.Row, .Column - 1) = Worksheets("jogos").Cells(.Row, .Column - 1) + 1
        Cells( .Row, 1).Activate
        CommandButton2.Visible = True
    Else
        MsgBox "Selecione o Jogo"
    End If
    end with
End Sub

[megs.rs]

I'm not that stupid...

…but ignorant enough to use »Application.Selection« 9 times in 15 lines of code!!

Code: Select all

Private Sub CommandButton2_Click()
    sel = Application.Selection
    If sel.Count = 1 And sel.Row >= 8 And sel.Column = 3 Then
        CommandButton2.Visible = False
        rem print Worksheets("configuracao").Cells(1, 2)
        rem ChDir Worksheets("configuracao").Cells(1, 2)
        rem print curdir()
        rem print "mame.exe " + "roms/" + sel
        Shell "runmame.sh " + "roms/" + sel , vbNormalFocus
        Worksheets("jogos").Cells(sel.Row, sel.Column - 1) = Worksheets("jogos").Cells(sel.Row, sel.Column - 1) + 1
        Cells( sel.Row, 1).Activate
        CommandButton2.Visible = True
    Else
        MsgBox "Selecione o Jogo"
    End If
End Sub

I really don't know what your point is in offending me.
I have 30 years of programming and I use comments to leave codes that I don't use anymore, but I might want to go back to using them saved.
Please, if it's to disturb, don't comment anything else!

[megs.rs]

With/end with can simplify and make code more readable, in case anyone isn't aware of it. Simplifying the last post:

Code: Select all

Private Sub CommandButton2_Click()
	sel =Application.Selection
    with sel
    If .Count = 1 And .Row >= 8 And .Column = 3 Then
        CommandButton2.Visible = False
        rem print Worksheets("configuracao").Cells(1, 2)
        rem ChDir Worksheets("configuracao").Cells(1, 2)
        rem print curdir()
        rem print "mame.exe " + "roms/" + sel
        Shell "runmame.sh " + "roms/" + sel , vbNormalFocus
        Worksheets("jogos").Cells(.Row, .Column - 1) = Worksheets("jogos").Cells(.Row, .Column - 1) + 1
        Cells( .Row, 1).Activate
        CommandButton2.Visible = True
    Else
        MsgBox "Selecione o Jogo"
    End If
    end with
End Sub

My goal here was not to make pretty code. And yes, it works.
I think you are getting hung up on insignificant details rather than paying attention that 'chdir' doesn't work properly.

[RoryOF]

Sometimes "insignificant details" can mask an underlying error.

[JeJe]

My goal here was not to make pretty code. And yes, it works.
I think you are getting hung up on insignificant details rather than paying attention that 'chdir' doesn't work properly.

I didn't test it - I'm just showing the use of with/end with - as it looked like someone might not know it and its been very useful to me knowing it.

It can be not worth the time rewriting or optimizing code sometimes and other times it is.