VBA to open file using Cell for the path

[Peter Davison]

I have this VBA code to import an excel sheet from a file into a sheet called sheet1 in the open file
It reads the folder path from Cell I9 in the sheet called Navigation. (Code in bold)
I can't understand why, when the macro runs it doesn't go to the path in Cell I9 but just goes to the Folder called Documents (which I believe is my One Drive default folder.
I think I need it to select it to go to the main file path but I'm not sure what is the code or where to put it?

Any help would be appreciated.

Sub CopyDataAndPasteOneStore()
Dim mainFilePath As String
Dim openFilePath As String
Dim openWorkbook As Workbook
Dim mainWorkbook As Workbook
Dim mainWorksheet As Worksheet
Dim sourceWorksheet As Worksheet
Dim lastRow As Long

' Read the main file path from Sheet "Navigation" Cell F8
mainFilePath = ThisWorkbook.Sheets("Navigation").Range("I9").Value

' Check if the main file is open
On Error Resume Next
Set mainWorkbook = Workbooks("Store Macro Analysis Basic v4.xlsm")
On Error GoTo 0

If mainWorkbook Is Nothing Then
MsgBox "Main file is not open.", vbExclamation
Exit Sub
End If

' Prompt user to select and open a file
openFilePath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", Title:="Select a file")

' Check if a file is selected
If openFilePath = "False" Then
MsgBox "No file selected. Operation canceled.", vbExclamation
Exit Sub
End If

' Open the selected file
Set openWorkbook = Workbooks.Open(openFilePath)

' Check if the opened file is correct
If MsgBox("Is this the correct file?", vbYesNo) <> vbYes Then
openWorkbook.Close False
MsgBox "Operation canceled.", vbInformation
Exit Sub
End If

' Reference the first sheet in the opened workbook
Set sourceWorksheet = openWorkbook.Sheets(1)

' Clear existing data in the main workbook before pasting new data
Set mainWorksheet = mainWorkbook.Sheets("Sheet1")
mainWorksheet.Range("A3:AZ" & mainWorksheet.Rows.Count).ClearContents

' Assuming the data is in Columns A to AZ and starts from Row 2
lastRow = sourceWorksheet.Cells(sourceWorksheet.Rows.Count, "A").End(xlUp).Row

' Copy data from the opened file
sourceWorksheet.Range("A1:AZ" & lastRow).Copy

' Paste the data into the main file
mainWorksheet.Range("A2").PasteSpecial xlPasteValues
mainWorkbook.Sheets("Sheet1").Activate
mainWorkbook.Sheets("Sheet1").Range("A1").Select

' Clear the clipboard
Application.CutCopyMode = False

' Close the opened file without saving changes
openWorkbook.Close False

' Activate the "Navigation" sheet and select Cell A1
mainWorkbook.Sheets("Navigation").Activate
mainWorkbook.Sheets("Navigation").Range("A1").Select

MsgBox "Data copied and pasted successfully.", vbInformation
End Sub

 

[offthelip]

You don't actually use the variable mainFilePath anywhere in your code after you set it, so it is not surprising nothing happens. to solve it have a look at this thread:
GetOpenFileName Default Directory?

 

[Peter Davison]

I have seen the code below -
ChDrive "G:\"
ChDir "g:\Underground\Operations\Job Allocation Sheets\Daily Job Status sheet A4\"
strFile = Application.GetOpenFilename

Where would I place or structure this in my code above?
This is what sits in Cell I9 -

C:\Users\pdavi\OneDrive\Documents\Cluster Insight Macro\Store Macro Analysis Beta v1\Data\One Store Basic Thanks for your assistance.

 

[offthelip]

try this:

' Read the main file path from Sheet "Navigation" Cell F8
mainFilePath = ThisWorkbook.Sheets("Navigation").Range("I9").Value
ChDrive "C:\"
ChDir mainFilePath
' or
ChDir mainFilePath & "\"   ' I am not sure which you will need

' Prompt user to select and open a file
openFilePath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", Title:="Select a file")

 

[Peter Davison]

That's just brilliant.
I've been on this for hours
It was the first option that was required.
you are most helpful
Very appreciated.
Thanks