How to make a macro go to a certain folder directly to complete the macro run

[agohir]

Hello,

I have the below code to extract data from a certain excel file (xlsx type) but I want to include two things in the macro.

1) The macro should directly open a certain folder directory for me to select the file from for example: "C\My Documents\"

2) And two if I press cancel instead of opening the document in the folder directory I want it to stop the macro and not show the debug window.

Macro:

Sub OpenPastCFFData()
'
' OpenPastCFFData Macro
'
Dim strSelectedLDCFile As String
Dim path As String


strSelectedLDCFile = Application.GetOpenFilename(", *.xlsx", , "Select Folder with desired Data Files")

Workbooks.Open Filename:=strSelectedLDCFile

Windows("Template Combination Tool.xlsm").Activate

Sheets("COMBINE DATA BUTTONS").Select

Range("M2").Select

'
End Sub


Any help highly appreciated.

 

[jayesh25]

small modification to your code.

Sub OpenPastCFFData()
'
' OpenPastCFFData Macro
'
Dim strSelectedLDCFile As String
Dim path As String




strSelectedLDCFile = Application.GetOpenFilename(", *.xlsx", , "Select Folder with desired Data Files")


If strSelectedLDCFile = False Then
MsgBox "The operation was cancelled", vbInformation
Exit Sub
End If


Workbooks.Open Filename:=strSelectedLDCFile


Windows("Template Combination Tool.xlsm").Activate


Sheets("COMBINE DATA BUTTONS").Select


Range("M2").Select


'
End Sub

 

[tonyyy]

And to address your first request, add this line just after the Dim statements...

ChDir "C:\MyDocuments"

Cheers,

tonyyy

 

[agohir]

And to address your first request, add this line just after the Dim statements...

ChDir "C:\MyDocuments"

Cheers,

tonyyy

Thanks for the help, it worked.

I have another question. There is a different macro in vba that imports data from multiple files and than I put additional macro lines to do a data quality check on certain columns. The issue is that the macro runs and doesn't give me an error but the data quality check column don't fill up, they are empty. As in the data quality check macro didn't work.

Here's the code and any help is appreciated. Thanks.

'Subcategories=TotalCategories in Admin Costs


Range("A2").Activate

Selection.CurrentRegion.Select

TotRows = Selection.Rows.Count
LastCol = Selection.Columns.Count

Range("A2").Cells(1, LastCol + 1).Select

ActiveCell.Formula = "=IF(SUM(C2:F2)<>G2,""Flag"","""")"

Range(Selection, Selection.Offset(TotRows - 2, 0)).Select
Selection.FillDown

Range("A2").Select

'Measure_ID


Range("A2").Activate

Selection.CurrentRegion.Select

TotRows = Selection.Rows.Count
LastCol = Selection.Columns.Count

Range("A2").Cells(1, LastCol + 1).Select

ActiveCell.Formula = "=VLOOKUP(B2,'Program Map'!$A:$B,2,FALSE)"

Range(Selection, Selection.Offset(TotRows - 2, 0)).Select
Selection.FillDown

Range("A2").Select

'Measure_ID


Range("A2").Activate

Selection.CurrentRegion.Select

TotRows = Selection.Rows.Count
LastCol = Selection.Columns.Count

Range("A2").Cells(1, LastCol + 1).Select

ActiveCell.Formula = "=VLOOKUP(E2,'LDC Map'!$A:$B,2,FALSE)"

Range(Selection, Selection.Offset(TotRows - 2, 0)).Select
Selection.FillDown