Posts by juergenkemeter

    Re: data extraction of workbooks, and collection in empty workbook


    Hi,
    Perhaps I can use the following code for my purpose. It opens every workbook in a folder, looks in a specific range, and extracts the data.


    Its problem is that the extracted data is overwritten by the next workbook extraction.
    Also, there is always a message displayed like "update links - yes or no", everytime a new workbook is being opened - dont know how to set this automatically to 'True'.




    cheers,
    Juergen

    Re: Chart of a dynamic range


    Hi,
    is it also possible to make a doube dynamic range, e.g.
    I have the Integers x and y.
    Now I want to write something like

    Code
    Range("Bx:My").Formula


    afterwards I want to change the integers, so that I access different ranges.


    Cheers
    Juergen

    one step further


    Hi,


    I have the following code. It extracts a data range of different xls files and writes them in a specific range of the current workbook.
    Problem: data is being overwritten, i.e. only the data of the last opened workbook is displayed in the target worksheet, instead of collecting all available data in different rows. Also, there are messages displayed when each workbook is being openend (update?/macros?), and I don't know how to turn them off, i.e. set them automatically to 'yes'.



    Hi,


    The following code loops through a folder with many workbooks, extracts the value of each cell 'A1', and then writes each workbook name in column A, and the corresponding cell value into column B of a new workbook.


    How can I modifiy this code to extract a whole range of data?
    Instead only extracting on cell value, I want to gather a specific rows, e.g.
    -B6:M9 into a new target sheet, named e.g. 'data'



    Re: data extraction of workbooks, and collection in empty workbook


    Hi,


    perhaps it is a good idea to break down my big pseudo code.


    My idea is to loop through the Folder with the many xls - workbooks. I have enclosed two examples.


    Then open each one of them, and look into their sheets named 'Sequence Data'.
    Then export available data written in certain rows/cols of these sheets:
    - B6:M9 should go into a new target 'Sheet1'
    - B11:M14 should go into target 'Sheet2'


    My problem is to process many workbooks, and write their output into a corresponding target sheet, one after another (collect them).


    Perhaps there is a way to combine the two code examples, from Will's Link: The first and the second approach (loop and manual selection of source ranges).



    Aem...Merry Christmas By the Way!! :)


    Cheers
    Juergen

    Re: data extraction of workbooks, and collection in empty workbook


    Hi,
    I tried your linked second approach code example and it worked fine to copy data into an open workbook.
    My problem right now is the IF - Clause. Sometimes the souce workbooks doesnt contain values in the specified range, and then there are empty cells produced in the 'Master' workbook.
    Also, I am not quite sure how to include the loop code for all workbooks in a folder, as I am trying to combine your linked first and second code examples.


    Juergen

    Hi!


    I have a folder 'Gene.File.Lists' which contains about 300 Excel workbooks, each one representing data for one gene.


    Each workbook has the same template structure, i.e. identically named Worksheets and same ranges for data input. I want to loop through all
    gene workbooks, open certain Worksheets, extract certain data ranges and insert these data sections for each gene in the Master workbook Sheets.


    I am looking for extracting various bits of data out of five different gene worksheets, and save this data into an empty Master Workbook
    The Sheet names are 'sequence data', 'sequence analysis', 'reagents', 'expression', 'information'.



    I enclosed an example gene workbook.



    Here is Pseudocode which illustrates what the later VBA Macro in the Master workbook should do. The first part creates new Sheets and columns in the empty Master Workbook. The second part should extract and insert these data sections into the corresponding columns in the Master Workbook.




    Hopefully my Pseudocode is more or less understandable, when you look at the example gene workbook I enclosed! If not, please ask.


    If someone has an idea how to do 'convert' this pseudocode, or a whole new solution, in VBA, I would be grateful for any help, since I lack the
    experience in VBA to do this.



    Cheers
    Jürgen

    Re: concatenate two cell values with VBA


    Hi!
    The code works fine. What do I have to change if I want to include column A into the origin dataset? I'm getting a little confused with the many offsets.


    And if you could write one or two sentences about how your code proncipally works, so that I can learn from it, thanks.


    Cheers,
    Jurgen

    Re: concatenate two cell values with VBA


    Hi,
    thx for your help so far.
    Perhaps it is possible to have a variable for checking if a colour has already been used?


    The comment type and comment text columns should differ for each colour.
    Yes, it would be good if only the cells that match in colour would be concatenated in value, and colour name into the two new columns.
    The worksheet has maximum FOUR different background colours in use.


    Jürgen

    Re: concatenate two cell values with VBA


    Hi Thomas,
    thanks for your code, it works fine.
    only one background color is used in a given row
    I found that in the next worksheet there are several (maximum three) background colours being used in a given row.


    I modified your code, adding colours.
    Is it possible to modify your code to look for more than one colour in each row, and write two new columns, containing comment type, i.e. the colour name and comment text, i.e. the (concatenated) cell values? So, including the colour index colum, there would be nine newly created columns, for each of the maximum of three possible comments.


    Cheers
    Juergen

    Re: export of hyperlinks and cell comments to .csv - file


    Hi,
    I wanted to change your code, to include columns A to M as data source to extract the hyperlinks and comments, and write these in column R and following.
    I also only wanted to apply the code for one Worksheet.
    Nevertheless, it seems I have made some mistake, as the data is not written correctly in the cells.


    Here is the changed VBA sub:

    Re: concatenate two cell values with VBA


    Hi,
    I enclosed an example Workbook.
    There, I have several rows, each with a header.


    In column M (comment type), I want to extract the background colour name, if a cell in a row is coloured.
    A UDF which can display the background colour name of a cell can be found within the following thread:

    Code
    http://www.ozgrid.com/VBA/ReturnCellColor.htm


    In column N (comment text), I want to display all cell values, which have been coloured with a background. Sometimes there are more cells in a row, then all the coloured cell values should be concatedated.
    E.g. for row 15, there should be the concatenated values of all yellow cells standing in the column N, best separated with some kind of character:
    'nu_DMP1_E0048_Scont.oaT_oDMP1_gamma isoform2_3241_sheep_ZDY_15-May-98_Contig'


    The value 'yellow' should be displayed in column M, for row 15.


    Another example, row 10: there should be displayed 'yellow' in column M (comment type), and '1058' in column N (comment text).



    cheers
    Juergen

    Hi,
    how can I concatenate the values of two (or eventually more, i.e. a Range) of Excel cells using VBA, also using a criteria, described below?
    The background criteria for how many cell values in a row should be concatenated and exported in a new column, is, if a cell in a column has a background colouring.


    Hope that I explained the situation convenient enough, any questions, please ask.


    Cheers,
    Juergen

    Hi,
    I have a column with different cell background colours.
    How can I export the colour name, rather than the colour index number, to a cell?


    Here is code to use for exporting the colour according to the index...

    Code
    Function ColourName(rng As Range)
        Select Case rng.Interior.ColorIndex
            Case 4:     ColourName = "Light Green"
            Case 5:     ColourName = "Blue"
            Case 7:     ColourName = "Pink"
            Case 10:    ColourName = "Green"
            Case 40:    ColourName = "Tan"
        End Select
    End Function

    Re: export of hyperlinks and cell comments to .csv - file


    Hawadere Stefan,


    thanks for your loop code, it works fine.
    what does the 'RC[0815]' in your following lines of code mean? Is it a VBA Expression, e.g. 'Row Count'?


    Cells(R, 2).Formula = "=GetAddress(RC[-1])"
    Cells(R, 3).Formula = "=GetCommentText(RC[-2])"


    And, would it be difficult to modify your code to include not only one single source column (A), but a Range of Columns (A to K), and then writing corresponding Hyperlink- and Comment - Columns from the Column N on (that means two columns per Source column)?


    cheers,
    Juergen

    Re: export of hyperlinks and cell comments to .csv - file


    Thanks for the code samples. These help for manual work, but as I have many Excel workbooks, I would need some kind of automatism.


    Here is a more convenient description of my situation:
    - I have a main folder, containing several subfolders.
    - Every (sub)folder contains one or more .xls - Workbooks.
    - Every Workbook contains one or more different Spreadsheets.
    - The workbooks contain some cells which have Hyperlink addresses to other,
    relating workbooks.
    - Some cells in wokrbooks contain comments, which must also be exported.


    My ultimate aim is to get ALL data, that means cell values, hyperlink addresses in cells, and comments, into corresponding PostgreSQL database table. Principally there are at maximum three different data per cell (value, hyperlink address, comment).


    Here is my idea.
    For every spreadsheet, read out cell values, cell hyperlink addresses (if there are any), and cell comments (if there are any).


    Then open a PostgreSQL database connection, and insert the cell values into a corresponding table.
    (which should already be created in the database before?)
    This table should also contain a column for the possible hyperlink addresses, and possible cell comment strings.


    So, this sounds like fun work... any help appreciated.
    Cheers
    Juergen

    idea - combining AddColorNum and AddGeneCategory


    EDIT:
    I have a solution now. I use the following code of AddGeneCategory(), a little altered.

    Code
    Function ColourName(rng As Range)
        Select Case rng.Interior.ColorIndex
            Case 4:     ColourName = "Tony genes"
            Case 5:     ColourName = "Nicks DD ESTs"
            Case 7:     ColourName = "Catagens DD ESTs"
            Case 10:    ColourName = "Janets PCR genes"
            Case 40:    ColourName = "Candidate genes"
            Case -4142: ColourName = ""
        End Select
    End Function



    Hi,
    after using AddColorNum(), I now have the Colour Index number in a column of my choice.


    Can I extend the AddColorNum(), to include the insert of the person's name according to the Colour Index Value?
    I tried to combine the Function AddGeneCategory() with AddColorNum(), but without a good result so far.


    How would an extended version of AddColorNum() look like, which first inserts the Color Index in the Cell, then selects the according Name:


    if the color index is
    -4142, --> final cell value should be '', i.e. empty.
    7, -->'Catagen DD ESTs'
    5 -->'Nicks DD ESTs'
    40 -->'Candidate genes'
    10-->'Janets PCR genes'
    4 -->'Tonys genes'


    cheers,
    Juergen