Import txt file into Excel 1

[teters]

Hi all,
I am trying to import a .txt file into Excel. The problem is that the txt file has subtotals in it and I want to make lines above the subtotals - to make them standout - like in a spreadsheet. Is there anyway to bring across the underlines, bold, italics when importing? Are there any special codes that Excel will recognize as underlines when it imports the file?

I am using Office 2000 on a Windows 2000 OS.

Any help is appreciated!
Scott

 

[Gavona]

I don't think there is.

Can you approach from a different angle? - Do the total lines share a charecteristic/set of characteristics? eg
blank in column b, not blank in column c, column a contains the string "total".
If so you can readily filter the imported data, apply special formating to the visible cells (the total lines) then take the filter off. (Consider applying styles to these lines rather than simply formatting them if you want to experiment with different styles to find the most effective.)

Thanks,

Gavin

 

[teters]

Gavin,
Thanks for the reply. I wish I could do that. All strings coming across can/will be different. The information that is being displayed will be laid out by the month Jan, Feb..., but I never know what month it will start with. We are importing accounting data (like a P&L statement)... totals, subtotals, but the accounts can be named just about anything (nothing is standard). We would like to include the lines in with the imported text, so the end user will not have to worry about adding them. Also, just as important, we would like to include the formulas in the text file. So when you import the txt file, the excel spreadsheet will automatically total the range. I'm starting to think that part is impossible. When you import into excel it imports everything as text. So the cell would read " '=sum(B1:B4) ". Anyway around that?
Thanks.
Scott

 

[Gavona]

So you CAN identify the total lines - they contain'=sum( in a particular column. We can easily format them then. Sounds like a regular task so once you hace sussed how to do it manually might be worth putting the macro recorder on and automating things - but that is a job for another day.

Can you alter the import data to contain a flag - in column A for rows that you want to be bold. Or is it just the total rows plus, say the row two lines later which might be the headings for the next group?

Or can you import raw data and then get excel to add subtotals etc etc.

Back to your problem
Edit, Replace, '=sum with =sum

Thanks,

Gavin

 

[teters]

Gavin,
You given me a great idea. I was looking at it the wrong way. If I can identify the total rows with the =sum then I can create a macro that will place a border above the cell with the =sum in it. Know, how can I identify the formula in a macro. Here's what I have so far...

ActiveWindow.DisplayFormulas = True
If Range("B6") = "=($B$3-$B$4)" Then
Range("B6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
End If
ActiveWindow.DisplayFormulas = False

but for some reason it is not picking up the formula. When I run the macro the B6 cell still picks up the number and not the formula.

Any more help is appreciated.
Thanks.
Scott

 

[teters]

Gavin,
I added the .HasFormula to the above code and it worked. Now I need to loop thru A1:Z200 to see if any of the cells has formulas in them. When a cell has a formula I will need to stop on that cell and put a border above it and then continue to loop. Do you have any code that will loop like that?

Thanks for all of your help.
Scott

 

[Gavona]

A few thoughts. Firstly this is the Office forum - it would be better to post VBA code queries in the VBA for Applications forum.

Without code, you can select all cells containing formulae:
Edit,goto,Special Formulas

My approach would be to switch macro recorder on. Do the above, apply borders, switch the recorder off, examine and modify the code.

The code generated will do lots of unnecessary things. For example:

Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    and another 20 odd lines

I would modify to:

Sub Macro1()
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

Or better still avoid unnecessary selections

Sub macro2()
    With ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

to modify cells other than those selected try:

Sub Macro3()
    For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
            With c.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            c.Font.Bold = True
            c.Offset(-1, 0).Font.Bold = True
    Next c
End Sub

Good luck with your project. If you get stuck with the code then start a new thread in the other forum, posting the code that you have and details of the problem.

Thanks,

Gavin

 

[teters]

Gavin,
Thanks again for the helpful post. That deserves a star. Also, I didn't realize there would be that much code involved. I will start in the other forum the next time. I will use the code posted above to finish my project.

Again, thanks for the time and effort.
thanks.
Scott

 

[Gavona]

Thanks, glad to have been of help.

I doubt if there needs to be any vba - unless you are regularly importing the file and are trying to automate the process.

Having done similar things to you in the past I would suggest that (at least for those of us who are less adept at code) it is helpful to try to find a solution without code first and then to automate it using macro recorder. Looping through all cells in a database is bound to be slower then using the inbuilt functionality.
Some of the stuff hidden in Edit,Goto,Special is excellent. (and there are toolbar buttons for some of these like select current region and select visible cells only). Couple this with autofilter (or even advanced filter) and a few really useful functions and you are away.

For your task I would have:
* created a couple of 'flag' columns to the left of the data.
* used autofilter to find the columns containing the formulae or whatever else I was interested in. make an entry in the flag column for all the visible cells
*If you wanted the rows two after each subtotal to be bold - eg they might contain a cost centre heading - then a simple formula in a4 like =if(b6="Total",""Header","") would identify the rows you were interested in. (better to use autofilter if you can though)
* having constructed this formula and saved it in a row above the start of my database I would copy it down to the entire database then immediately convert it to values.

*thus you have some columns to easilly select the rows that you want to manipulate/format.

Just to repeat from my first post: Consider applying styles to these lines rather than simply formatting them if you want to experiment with different styles to find the most effective. Styles are really powerful ways to help you play with the format quickly. You just modify the style once from the menu and every cell that uses it changes.


Thanks,

Gavin