Insert Excel Cell to VBA Word (Textbox) Bookmark

[ng_34]

Hello,

I have a word document, which contains an user interface; in the user interface there are many textboxes. At the moment, the user may enter information in the textbox and it will appear in the Word document using "Bookmark" at the various locations of the document.

However, I would like to modify this code by automatically filling the textbox in the User Interface from an Excel Spreadsheet. The cell locations would be the same in each Excel Spreadsheet, however the file names and the cell content will be different, thus the user needs to be choose (prompt - open file) an Excel Spreadsheet each time the User opens the word file.

Can someone please help me or provide suggestions?

Thanks in advance.
nG

[fumei]

How far along are you? Do you know how to make an instance of Excel, how to open it and get the information?

[ng_34]

Well, I do not know how to prompt for the Excel Spreadsheet... nor do I know how to use the specific cell and place it in the textbox. If you can help me with this it would be great.
Thanks




How far along are you? Do you know how to make an instance of Excel, how to open it and get the information?

[fumei]

What have you tried so far? There are lots of examples out there. Or here. We are willing to help but you need to do some work yourself.

As for prompting... do you want a prompt just asking for a filename? Some sort file selection process?

Do you know how to point to a specific cell when you DO have the Excel file open? In other words do you know how to use Excel?

[ng_34]

Hey.. so this what I have so far.. I have this in my userform (should it be in module?) .. nothing really happens when this is in there.. do I have to call it in the program?

'**********************************************************'
'Use to import a result from a Project Specifications doc.
'**********************************************************'
Public Sub ImportFromProjectSpec()
pathfname = Application.GetOpenFilename(fileFilter:="Configurator Ready Model (*.xls), *.xls")

If pathfname <> False Then

fname = PathExtract(pathfname)

WorkbookIsOpen = False

For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = fname Then
WorkbookIsOpen = True
Exit For
End If
Next i

'On Error GoTo ErreurFatale
If WorkbookIsOpen = False Then
Workbooks.Open pathfname
End If
'..........................................

If Workbooks(fname).Sheets(Workbooks(fname).Sheets.Count).Name <> "Configurator Ready" Then
'If WorkbookIsOpen = False Then
' Workbooks(fname).Close
'End If
MsgBox "The selected File is not a Project Specs"
ThisWorkbook.Activate
Exit Sub
End If

ThisWorkbook.Activate

'""""""""""""""""""""""""""""""""""""""""""""
'Clear the contents
'""""""""""""""""""""""""""""""""""""""""""""
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""

'""""""""""""""""""""""""""""""""""""""""""""
'First fill and determine the good model
'""""""""""""""""""""""""""""""""""""""""""""
UserForm1.TextBox1.Value = Workbooks(fname).Sheets(Workbooks(fname).Sheets.Count).Cells(1, 1).Value
UserForm1.TextBox2.Value = Workbooks(fname).Sheets(Workbooks(fname).Sheets.Count).Cells(1, 2).Value
UserForm1.TextBox3.Value = Workbooks(fname).Sheets(Workbooks(fname).Sheets.Count).Cells(2, 1).Value
UserForm1.TextBox4.Value = Workbooks(fname).Sheets(Workbooks(fname).Sheets.Count).Cells(2, 2).Value

End If
ErreurFatale:
If Err.Number <> 0 Then
MsgBox "N° erreur:" & Err.Number & vbLf & Err.Description
End If
End Sub

[fumei]

This is code that is executing from Excel (using Excel VBA code) - NOT from Word. So you need to determine where you are executing your code from.

Are you running from Excel and getting an instance of Word, or are you running from Word and getting an instance of Excel?

[ng_34]

Hey,

I'm using Word and have my code in Word, however I need to prompt for the Excel File. What do I have to change to make it into a Word code? Is there anything missing?

Thanks for the help and taking time to read my post.

[ng_34]

Ok, so I was able to call an instance from Word (It asks for an excel file), however I'm still unable to insert the specific cell information to the textbox.

The following code is in "This Document"

Private Sub Document_Open()

If ThisDocument.Variables("Name").value = "< Project >" Then

'Open Dialog
Dim diaopen As FileDialog

Set diaopen = Application.FileDialog(msoFileDialogOpen)

With diaopen
.AllowMultiSelect = False
.Filters.Add "Excel Automated File", "*.xls, *.xlsx, *.xlsm", 1
.FilterIndex = 1
.InitialFileName = "*.xls"
.title = "Open Excel Automated File from E3"

If .Show = -1 Then
filePath = .SelectedItems(1)
UserForm1.Show
Else
MsgBox "No File selected - Go back to document!", , "File Error"
End If
End With

End If
End Sub


How do I get the cell info from the Excel File and insert it into my Textbox???

??
UserForm1.TextBox1.value = Workbooks(Name).Sheets(Workbooks(Name).Sheets.Count).Cells(1, 1).value??
TextBox1.value = Name.Sheets(Sheet1).Cells(1, 1).value

Neither Works :(

Thanks once again!

[ng_34]

Hey I solved the problem...

Here is the code!!!

'****************************************
' Load File
'****************************************
Private Sub CommandButton9_Click()
'Open Dialog
Dim diaopen As FileDialog
Dim wb As Excel.Workbook
Dim objExcel As New Excel.Application
Dim filePath As Variant
Set diaopen = Application.FileDialog(msoFileDialogOpen)

With diaopen
.AllowMultiSelect = False
.Filters.Add "Excel Automated File", "*.xls, *.xlsx, *.xlsm", 1
.FilterIndex = 1
.InitialFileName = "*.xlsx"
.title = "Open Excel Automated File from E3"

If .Show = -1 Then
filePath = .SelectedItems(1)
Else
MsgBox "No File selected - Please Close the document and restart!", , "File Error"
End If
End With

Set wb = objExcel.Workbooks.Open(filePath)
TextBox1.value = wb.Sheets("Sheet1").Cells(1, 1).value
TextBox2.value = wb.Sheets("Sheet1").Cells(2, 1).value
TextBox3.value = wb.Sheets("Sheet1").Cells(3, 1).value
TextBox4.value = wb.Sheets("Sheet1").Cells(4, 1).value
TextBox5.value = wb.Sheets("Sheet1").Cells(5, 1).value
TextBox6.value = wb.Sheets("Sheet1").Cells(6, 1).value
TextBox7.value = wb.Sheets("Sheet1").Cells(7, 1).value

wb.Close
Set wb = Nothing
End Sub