Solved: Browse for excel file and import a range of cells

[brent.fraser]

Hello there peoples,

I am almost there in creating a master report that pulls information from an Excel spreadsheet and compiles it into Word.

Here's what I have so far:
Public Sub importExcelData()
Dim workBook As workBook
Dim dataInExcel As String
Application.ScreenUpdating = False
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.xlsx", vbNormal)
Set workBook = Workbooks.Open(strFolder & "\Book1.xlsx", True, True)
dataInExcel = workBook.Worksheets("Sheet1").Range("A1").Formula
Dim oCC As ContentControl
Set oCC = ActiveDocument.SelectContentControlsByTitle("box1").Item(1)
oCC.Range.Text = dataInExcel
workBook.Close False
Set workBook = Nothing
Application.ScreenUpdating = True
End Sub
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

It's sort-of working. What I would like to do is browse to a specific Excel file since the name of the file is going to change all the time (I have it hard-coded as "Book1.xls" above. The browse function I found only browses to the folder and not a particular file. I found the following code but it seems to only work in Excel:

Sub GetFilePath()
FilePath = Application.GetOpenFilename("Text Files (*.xls*), *.xls*")
If FilePath <> False Then
Dim oCC_filePath As ContentControl
Set oCC_filePath = ActiveDocument.SelectContentControlsByTitle("filePath").Item(1)
End If
End Sub

I am still looking for the right code but it has been difficult to locate so far.

Lastly, in the above code, it is looking for the following range:
dataInExcel = workBook.Worksheets("Sheet1").Range("A1").Formula


As soon as I change the range from A1 to A1:A6, it stops working. I have even tried to name the range in Excel and that doesn't work. Does anyone have a suggestion?

Thanks in advance for your help.

B.

[newbie101]

Use the Application.FileDialog(msoFileDialogFilePicker)
Sub GetFilePath()
Dim fd As FileDialog
Dim strExcelFile As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the picture"
fd.Filters.Add "Excel Files", "*.xls; *.xlsx", 1
fd.Filters.Add "All Files", "*.*"

If fd.Show Then
strExcelFile = fd.SelectedItems(1)
End If
If strExcelFile <> False Then
Dim oCC_filePath As ContentControl
Set oCC_filePath = ActiveDocument.SelectContentControlsByTitle("filePath").Item(1)
End If
End Sub

[brent.fraser]

Thanks newbie101,

It's getting close now. I can browse to the file and select it. Now I would like to have the file and path we just browsed to included in the following code:
Set workBook = Workbooks.Open(strFolder & "\Book1.xlsx", True, True)

I tried the following to no avail:
Set workBook = Workbooks.Open(strExcelFile, True, True)
Basically I am wanting the file we browsed to, to be the one that is referenced to in the workbooks.Open code above.

Still plugging away.

B.

[brent.fraser]

Well, I am getting closer. I added "MsgBox strExcelFile" to the below code and I get the message with the file path (exactly what I wanted).

Public Sub GetFilePath()
Dim fd As FileDialog
Dim strExcelFile As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the file"
fd.Filters.Add "Excel Files", "*.xls; *.xlsx", 1
'fd.Filters.Add "All Files", "*.*"
If fd.Show Then
strExcelFile = fd.SelectedItems(1)
MsgBox strExcelFile
End If
End Sub
When I reference strExcelFile in the below code (I also have strExcelFile show up in a message), the message is blank.
Public Sub importExcelData()
Dim workBook As workBook
Dim dataInExcel As String
Application.ScreenUpdating = False
MsgBox strExcelFile
Set workBook = Workbooks.Open(strExcelFile, True, True)
dataInExcel = workBook.Worksheets("Sheet1").Range("A1").Formula
Dim oCC_box1 As ContentControl
Set oCC_box1 = ActiveDocument.SelectContentControlsByTitle("box1").Item(1)
oCC_box1.Range.Text = dataInExcel
workBook.Close False
Set workBook = Nothing
Application.ScreenUpdating = True
End Sub

So close.... but the file path in strExcelFile is not being passed from the first function to the second one. I know it's an easy solution but my brain is a bit fuzzy right now.

[brent.fraser]

I got it.... just had to declare the strExcelFile as public and it worked!

Thanks for the point in the right direction newbie101.

[Alex675]

Hello guys,

I am new in VBA. Could you please let me know how I can get a directory of a file via a common button such as "Browse" and paste that directory to a line of my codes.
Thank you so much