help importing data

[john smithmyer]

I have a data source with 20 fields. I created an export “template” that exports 8 of the 20 fields to a tab delimited text file.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I also have an Excel spreadsheet (with visual basic code) that imports this data ( 8 fields) and processes it into a report.<o></o>
<o></o>
The eight fields are:<o></o>
Date 05/26/2012<o></o>
View Good<o></o>
Temp 78<o></o>
Size 8<o></o>
Cap Yes<o></o>
Bad No<o></o>
Run Yes<o></o>
Color Red, Blue, Green<o></o>
<o></o>
When exported from the data source, if Color has more than one entry it creates duplicate fields for each one like this:<o></o>
Date View Temp Size Cap Bad Run Color Color Color<o></o>
05/26/12 Good 78 8 Yes No Yes Red Blue Green<o></o>
<o></o>
Any ideas on how I can import this data into my Excel application which would combine the “like” fields into one before processing?<o></o>
<o></o>
Thanks as always for your kind assistance!<o></o>
<o></o>
John<o></o>

 

[john smithmyer]

Funny enough, I get the same error.

I don't get it!

Again, thanks for your very kind help in finding a solution to this problem.

Best Regards,
John

 

[Joe4]

It may have to do with how you are brining the data in (it looks like you may be linking instead of importing). Maybe it thinks you are trying to update the original data source and doesn't like that.

What iof you trying importing your file instead?

 

[john smithmyer]

Joe,

Thanks for your continued help.

I think I have an idea. My imported data runs from column A to M.

I also have data and formulas from column O to Z.

How could your code be altered to only go to coulmn M rather than to the last coulmn.

I think your code is getting messed up by stuff in columns O to Z.

Does that make sence?

 

[Joe4]

Maybe this (new lines highlighted in red)?

Sub FixData()
 
    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim myRow As Long
    Dim myCol As Long
    Dim myString As String
 
    Application.ScreenUpdating = False
 
'   Find last row of data
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
'   Loop through each row
    For myRow = 1 To myLastRow
'   Find last column of data
        myLastCol = Cells(myRow, Columns.Count).End(xlToLeft).Column
'   If goes past column H, combine last columns
        If myLastCol > 8 Then
            myString = ""
            [COLOR=red]If myLastCol > 13 Then myLastCol = 13[/COLOR]
            For myCol = 8 To myLastCol
                [COLOR=red]If Len(Cells(myRow, myCol)) > 0 Then[/COLOR]
                    myString = myString & Cells(myRow, myCol) & ","
                [COLOR=red]End If[/COLOR]
            Next myCol
            Cells(myRow, 8) = Left(myString, Len(myString) - 1)
            Range(Cells(myRow, 9), Cells(myRow, myLastCol)).ClearContents
        End If
    Next myRow
 
    Application.ScreenUpdating = True
 
End Sub

 

[john smithmyer]

Joe,

Thanks!!!!!!!!!!!!!!!!!!

That did it.

Thank you and MeExcel for all you do to help us.

Very Best Regards,
John