Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to automate datasheet stuffing?
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 shouldnt 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 doesnt find the source document and template for a given language €“ sometimes we dont 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to automate datasheet stuffing?
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 "Karl" wrote: 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 shouldnt 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 doesnt find the source document and template for a given language €“ sometimes we dont 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Population org char from a datasheet | Excel Discussion (Misc queries) | |||
Saving data to a form datasheet | Excel Discussion (Misc queries) | |||
Text box linked to datasheet | Charts and Charting in Excel | |||
Automate Excel to powerpoint - Graphs along with Datasheet (not workbook) | Charts and Charting in Excel | |||
Where is the viewing datasheet button? | Charts and Charting in Excel |