Posts by rem1224

    Here's a different solution that worked for me:


    1. Open the HTML document and copy the table to the clipboard.


    2. Open Notepad and paste the clipboard into a new Notepad file - Save the notepad file (example: testfile.txt)


    The file should probably look something like:


    1:24 4:35 12:56 62:34
    12:34 6:15
    2:33 6:50 9:30
    etc


    depending on how many rows/col were in the HTML table


    3. Open the testfile.txt in Excel - you should get a text import wizard - - Pick 'Delimited' as the data type, on the next screen set the delimiter to 'space' - on the next screen set the column data format to 'text' for each column - then click 'finish'


    4. The data should now be Text Only in the excel sheet. You can copy and paste this data into the actual worksheet where you need it (make sure the destination cells are formatted as Text)



    .....Ralph

    Going back to Will's question - Where is the data you are pasting into the Excel sheet coming from (where did you copy it from).


    If it is plain text - like coming from a file like "filename.txt", then I'm really not sure why the earlier solution is not working.


    If you are copying the data from another Excel sheet or another spreadsheet / database program and the data in that program is actually in time format, then you are not really copying "4:35" but the time value (the decimal time as you called it) so that is the only thing that the clipboard has to paste in the cell - regardless of how its formatted.


    If that is the case, you will have to convert the time value to a text string BEFORE you copy and paste it.


    If that can't be done, you can paste it into excel as the time value, then convert it to text, then copy and paste values on the text to get rid of the conversion formula - - see the following sheet with formulas


    .....Ralph


    <SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - Book2</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl2002 XP : OS = Windows XP </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;(<U&gt;A</U&gt;)bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb605117&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb202339&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362&gt;<OPTION value='=TEXT(A3,"h:mm")' selected&gt;B3</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value='=TEXT(A3,"h:mm")' name=txbFb150492&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"&gt;Time&nbsp;Formatted</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"&gt;Text&nbsp;Converted</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"&gt;Pasted&nbsp;Value</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;This&nbsp;number&nbsp;is&nbsp;Time</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;This&nbsp;is&nbsp;=TEXT(A3,"h:mm")</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;This&nbsp;is&nbsp;Copy&nbsp;B3&nbsp;-&nbsp;Paste&nbsp;Values&nbsp;to&nbsp;C3</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;3</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #0000ff; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;4:35</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #0000ff; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;<A onclick="document.formFb202339.sltNb447362.options[0].selected=true; document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value;" href="#javascript:void(0);"&gt;4:35</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #0000ff; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;4:35</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;4</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet1</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker 2.41] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;

    You can hide any sheet with menu Format / Sheet / Hide. At least 1 sheet must be visible.


    You can show a blank sheet behind your userform and change the "fill Color" of all cells to any color to make it look like a solid background.


    .....Ralph

    You can set up a Query
    Data / Import External Data / New Database Query


    If not already set up, you will have to set up the Data Source to be "Microsoft ODBC for Oracle". Then you should be able to attach to the data source and write the query in SQL.


    .....Ralph

    Hi -


    You can put a check box / combo box in a cell, then select / highlight the cell, grab the anchor (bottom right corner), left-click and drag down just like you would for a value or formula.


    All formatting control will be duplicated, including the cell reference - copying won't update the cell reference address like a formula does - so you still have to edit the controls individually.


    .....Ralph

    Hi Beeker,


    Have a look at the attached.
    I added a macro to your file to copy over all the data on the Week End sheet to the individual sheets.


    If the employee does not have a sheet, a new sheet is created and formatted (the sheet has to be named the exact same as the data in Col A).


    A new line is added on to the end of the employees sheet with the current week and data.


    Hope this helps


    .....Ralph

    A couple of other ideas,


    Check the icon / shortcut you're using to start excel and see if somehow the filename "normal.dot" got tagged onto the end of the command line.


    Check in Excel under Tools / Options / General and make sure the field for "At startup, open all files in:" is blank.


    Check the Visual Basic Editor (Alt-F11 from within Excel) for any addins that are trying to load that file.


    .....Ralph

    Hi Peter,
    It's a bit more difficult than normal because you have a lot of records that are very similar (same surname, forename, prefix, etc).


    See the attached sheet. I added an column to each worksheet to create a KEY for each record (combined surname, forename, prefix, and job title to try to get something unique). Next used the VLOOKUP function to pull the source and abc code from the old to the new. The #NA shows where there is no match.


    Hope this gets you started.


    .....Ralph

    It may be overkill on the calculations and slow things down too much, but you could put the CalculateFull in the change event macro for that sheet.


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CalculateFull
    End Sub


    After you change the color of the cell and leave the cell, the function will update.


    .....Ralph

    Hi Kelly,


    You could try something like this:


    Code
    Sub EditFilteredRange()
    Dim rng As Range
    Set rng = Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
       For Each cell In rng
             ' Your Code Goes Here
       Next cell
    End Sub


    assumes that Row 1 is your header row, so it doesn't pick it up.


    Hope this helps


    .....Ralph

    Hi,


    Which variables in the routine do you want to limit?


    In your For/Next loops you can skip numbers by using:
    For j = 1 To 30 Step 3
    This will use 1,4,7,10, etc in the loop. Not sure if that is what you wanted.


    .....Ralph

    Hi CoMaboy,


    Sounds like the normal.dot file is in the wrong location. Look for a subdirectory called "xlstart". It will be somewhere in the tree below Microsoft Office. The normal.dot file should not be in that directory. (I put a copy there to test and got the same error as you mention).


    Normal.dot is a Word file where the default document parameters (tabs, margins, fonts, etc) are stored. It's usually in a directory called "templates".


    Hope this helps


    .....Ralph

    Hi -


    Is your word document all formatted like your example, i.e.


    AA456789 GENE
    BB123455 PAUL
    CC123 JOHN
    ZZ9999 JANE


    etc.


    If so, it would be a lot easier to perform a "convert text to table" operation in Word, then copy and paste the table into excel. Then you could do your cell merges with VLOOKUP and CONCANTENATE functions.


    (is this the same question you posted today under VBA: Help with Excel and parsing a txt document ?)


    Hope this helps


    .....Ralph

    Hi Bryan,


    I mis-read your original post - thought you were trying to do something else - -anyway - -


    You might try the following code, it left justifies text and centers numbers (doesn't matter what the length of the entry is)


    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim myRange As Range, temp As Integer


    Set myRange = Range("B:B")
    If Intersect(Target, myRange) Is Nothing Then Exit Sub


    On Error GoTo ItsText
    temp = Target.Value * 1 'test to see if it's a number

    Target.HorizontalAlignment = xlCenter
    Exit Sub


    ItsText:
    Target.HorizontalAlignment = xlLeft


    End Sub



    .....Ralph