VBA - Clarification

[Ram Chandran]

Hi Guys,

When i run the vba code it automatically close all the excel file. Please find the my attached File with code.
The file need to open its size more than 120MB
Can u pls help me to resolve this.

Thanks in advance,
Ram

 

[Luke M]

Your workbook has four code modules, and 3 macros. Can you narrow it down as to what macro/line of code you want help with?

 

[Ram Chandran]

Hi Luke M,

Its Macro 3, pls find the attachment. When i Run this Macro its automatically close.

Thanks in advance

 

[Logit]

Using two local files on my desktop, the following worked fine here :

Sub Macro3()
'
' Macro1 Macro
'

'
    Dim aa, bb, hh
    aa = Workbooks("Automate Daily Cost.xlsm").Sheets("Main").Range("D3").Value
    bb = Workbooks("Automate Daily Cost.xlsm").Sheets("Main").Range("D8").Value
    hh = Workbooks("Automate Daily Cost.xlsm").Sheets("Main").Range("D6").Value
    
    Workbooks.Open ("C:\Users\logit\OneDrive\Desktop\Testing2.xlsb")
    Workbooks.Open ("C:\Users\logit\OneDrive\Desktop\Testing 3.xlsb")
    
End Sub

I believe the following is incorrectly formulated and requires changing :

Daily Cost Calculation Reports\" & hh)

I believe referring to "hh" at the end of the line of code is creating the issue.

 

[Ram Chandran]

Hi Logit,

Thanks for your response, i refer "hh" as D6 cell value which is dynamic values. I need to change the values dynamically.
Can u pls help to resolve.

Thanks in advance.

 

[Marc L]

Hi ! « dynamic » ?‼​

​

According to your attachment static cells address a VBA demonstration how to check path & files :​

Sub Demo1()
  Const A = "Alert", C = 16
    Dim P$, V, F$
   With Sheet1
        P = .[D3&IF(RIGHT(D3,1)="\","","\")]
        If Dir(P, C) <> "." Then MsgBox "Invalid path !", C, A: Exit Sub
    For Each V In [{"D6","D8"}]
        F = P & .Range(V).Text
        If Dir(F) = "" Then MsgBox "Invalid file in " & .Name & "!" & V, C, A: Exit Sub
        Workbooks.Open F
    Next
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Logit]

Try the following :

Sub Macro3()
    ' Declare variables
    Dim aa As String, bb As String, hh As String
    
    ' Assign values from the cells to variables
    aa = Workbooks("Automate Daily Cost.xlsm").Sheets("Main").Range("D3").Value
    bb = Workbooks("Automate Daily Cost.xlsm").Sheets("Main").Range("D8").Value
    hh = Workbooks("Automate Daily Cost.xlsm").Sheets("Main").Range("D6").Value
    
    ' Open workbooks using variables
    Workbooks.Open (aa) ' Using variable 'aa' to open a workbook
    Workbooks.Open (bb) ' Using variable 'bb' to open a workbook
    Workbooks.Open (hh) ' Using variable 'hh' to open a workbook
End Sub

 

[Marc L]

Logit, according to your previous post the first Open codeline will crash the VBA procedure …​

​

 

[Logit]

Taking a step out on a limb here. My goal with this next macro is to simplify your workload by minimizing
how much you need to enter to obtain your desired result. I resorted to variable for your files.

Macro in a regular module :

Sub MacroVariablesOpen()
    Dim aa As String, bb As String, hh As String
    Dim ws As Worksheet
    Dim cell As Range
    Dim fileOpened As Boolean

    ' Define workbook paths
    aa = "C:\Users\logit\OneDrive\Desktop\Testing2-A.xlsb"
    bb = "C:\Users\logit\OneDrive\Desktop\Testing2.csv"
    hh = "C:\Users\logit\OneDrive\Desktop\Testing 3.xlsb"

    ' Reference the active sheet
    Set ws = ThisWorkbook.ActiveSheet

    ' Initialize flag
    fileOpened = False

    ' Loop through Column D
    For Each cell In ws.Range("D2:D100") ' Adjust range as needed
        If Not IsEmpty(cell.Value) Then
            Select Case Trim(UCase(cell.Value))
                Case "AA"
                    Workbooks.Open (aa)
                    fileOpened = True
                Case "BB"
                    Workbooks.Open (bb)
                    fileOpened = True
                Case "HH"
                    Workbooks.Open (hh)
                    fileOpened = True
                Case Else
                    Debug.Print "No match for: " & Trim(UCase(cell.Value))
            End Select
        End If
    Next cell

    ' Notify user
    If fileOpened Then
        MsgBox "Workbooks displayed in Column D have been successfully opened!", vbInformation
    Else
        MsgBox "No matching workbook paths found in Column D.", vbExclamation
    End If
End Sub

You can enter any variable into any of the cells you are using in Column D for this purpose. You can enter only one variable, or two in two different cells or three in three different cells. Modifying the code you could open many more workbooks.