Retrieving info from an Excel database using a Word Template

[DaffyDukk]

I currently have an excel database that holds sort codes, product information, Prices etc. on products that we sell. What I need to be able to do is create a word template that can pull off specific information for a breakage report. EG.

Using sort code as my unique field, gain access to prices etc through Word.

One other snag though. The sort code can't be shown on the printout of the breakage report!(This is not overly important at the moment. Main problem is the retrieval from the excel database!)

Thanks in adavnce for any help you can provide

 

[DarkSun]

Sorry to ask this question, but!

Is it a Excel Workbook
Or
Is it a Access Database

 

[DaffyDukk]

Excel Worksheet
(see Email sent to you)

 

[DarkSun]

I have replied to your email. Basically I will try and help but the easiest way to help is to see example data. So if you could send the Worksheet and Document you are trying to populate I will try and set it up for you.

 

[DarkSun]

I feel I need to post more code that I write for people on the Forum to help others. (Pointed out by dsi).

This is some code I wrote for DaffyDukk to get data from Excel and insert it into a combobox in Word. The code should be placed in the word document. (For this to work you would need the datasheet I was working with).

Private Sub test()
Dim Codes() As String
Dim LastReacord As Integer
Dim CodesHeader As String
Dim DataSheet As String
Dim DataRange As String

CodesHeader = "Jul99" 'Header Name in Excel sheet for Codes
DataSheet = "C:\officemacros\datasheet.xls" 'Filename and Path of Excel sheet
DataRange = "CodeData" 'Name of Data Range in Sheet

With ActiveDocument.MailMerge
.MainDocumentType = wdCatalog
.OpenDataSource Name:=DataSheet, ReadOnly:=True, Connection:=DataRange 'Opens DataSource
End With


LstIndex = 0

With ActiveDocument.MailMerge

Do While .DataSource.ActiveRecord <> LastRecord

LastRecord = .DataSource.ActiveRecord

ReDim Preserve Codes(LstIndex)

.ViewMailMergeFieldCodes = False
.DataSource.ActiveRecord = wdNextRecord

If .DataSource.DataFields(CodesHeader).Value <> &quot;&quot; Then
Codes(LstIndex) = .DataSource.DataFields(CodesHeader).Value
LstIndex = LstIndex + 1
End If

Loop

End With

ThisDocument.CmbCodes.List = Codes() 'Fills ComboBox with Codes
End Sub

 

[DarkSun]

This is the final bit of code for this thread. This sub returns the description of the product which matches the code selected in the combo box.

Private Sub InitialSettings()
CodesHeader = &quot;Jul99&quot; 'Header Name in Excel sheet for Codes
DescriptionHeader = &quot;LIGHT_TOOLS&quot; 'Header Name in Excel sheet for Description
End Sub


Private Sub CmbCodes_Change()
Call InitialSettings
With ActiveDocument.MailMerge

.DataSource.ActiveRecord = .DataSource.FirstRecord
.ViewMailMergeFieldCodes = False

If .DataSource.FindRecord(FindText:=CmbCodes.Value, Field:=CodesHeader) = True Then
LblDescription.Caption = .DataSource.DataFields(DescriptionHeader).Value
End If

End With

End Sub