How to automate datasheet stuffing?

K

Karl

Hi,

My department has just taken over responsibility for the creation of a
number of datasheets in several languages. These are all produced in Excel.

We draw our data from tab separate value text files. These contain lists of
specifications that shouldn’t vary from one file to another. However, the
order the specs are in does vary, and sometimes a given spec is missing.

What we want to do, is find some way to automate the stuffing of datasheets
by getting Excel to look for the name of each specification, then to copy the
data in the cell seven cells to the left into the right field in our data
sheet. And we need to do this for all languages

So, instance, we have our template datasheet and our source text file open
in the same instance of Excel. Excel looks at the document and sees that cell
J6 contains the value “Product Nameâ€. In response, Excel copies the value in
cell Q6 of the source document to cell B1 of the template datasheet. It then
carries on and does the same for all the standard values it can find in the
source, population the datasheet with all the required values that can be
found in the source document.

In the best case scenario we need the script, or batch file or whatever tool
we need to start with one source document, stuff the relevant language
template, then move on to the next source document and language template. We
also need to it not to get stuck and just stop if it doesn’t find the source
document and template for a given language – sometimes we don’t have
datasheet for every language.

I know this is incredibly complicated – but if anyone could even tell me
what I need to learn to find a solution that would be great.

Thanks

Karl
 
B

Billy Liddel

Karl
I'm not sure if this is suitable, but someone supplied the following data
that he wanted tablulated

Title Intro to Cpt
Author James, Page
publisher M-HILL
subject CPT
totalpage 311
isbn 123

Title Basic A/C
Author Ben William
publisher JWS
isbn 721


Title Modern Phy
publisher P-HALL
subject PHY
totalpage 466
isbn 265

As you can see not all the data is the same for each record. I copied this
into sheet 1 and this macro copied the data into sheet3 (Formatted). It uses
an array for the Column Headings, taken from the available data and uses this
to copy the data correctly. Of course you wold have to change the array for
each lanuage.

Sub FormatData()
Dim x, c, r As Long, nr As Long, i As Long
'Dim col As Integer, ncol As Integer
Dim rng As Range
Worksheets("Sheet1").Select
Application.ScreenUpdating = False
' set up the headers to look up
x = Array("Title", "Author", "Publisher", "Subject", _
"TotalPage", "ISBN")
With ActiveSheet
nr = .Cells(.Rows.Count, 1).End(xlUp).Row
r = 1

Set rng = Range(Cells(1, 1), Cells(nr, 1))
End With
Worksheets("formatted").Select
ActiveSheet.Cells.Clear
Range("A1:F1") = x
On Error Resume Next

For Each c In rng
If c = "Title" Then
r = r + 1
End If

' check column A against headers X
For i = 1 To 6
If UCase(x(i)) = UCase(c) Then
Cells(r, i) = c.Offset(0, 1)
End If
Next i
Next c
Range("A1:F1").Select
Selection.Font.Bold = True
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A3").Select

Application.ScreenUpdating = True
End Sub

Perhaps you can adapt this to suit your needs. Copy the data and macro into
a new book and see if it is what you want.


Peter
 
Top