On Jun 26, 8:00*am, spacemancw wrote:
On Jun 25, 3:51*pm, Mike H. wrote:





This should work:

Option Explicit
Option Base 1

Sub SetupNewSheet()
Dim FF As Long
Dim X As Double
Dim Y As Double
Dim L As String
Dim Z As Double
Dim DataArray(5000, 10) As Variant
Dim Nbr As Integer
Dim StartRow As Double
Dim StartCol As Double
Sheets("hasdata").Select

StartRow = 1 'Starting Row
StartCol = 4 'Starting Column
X = StartRow
Do While True
* * If Cells(X, StartCol).Value = Empty Then Exit Do 'assume col 1 always
has data
* * For Z = 1 To 10
* * * * Cells(X, StartCol + Z - 1).Value = Empty 'blanks out all previous data
* * Next
* * X = X + 1
Loop
'now open your new data file

FF = FreeFile()
Open "c:\temp\thefile.txt" For Input As #FF * '<--Change File name as
required.

Do While Not EOF(FF)
* * Line Input #FF, L
* * *Nbr = Nbr + 1
* * *For Y = 1 To 9
* * * * *Let DataArray(Nbr, Y) = Left(L, InStr(L, ",") - 1)
* * * * *Let L = Right(L, Len(L) - Len(DataArray(Nbr, 1)) - 1)
* * *Next
* * *Let DataArray(Nbr, 10) = L

Loop
Close #FF

For X = 1 To Nbr
* * For Y = 1 To 10
* * * * Cells(StartRow + X - 1, StartCol + Y - 1).Value = DataArray(X, Y)
* * Next
Next

End Sub

Great thanx ..... will try this later today

I was looking at another way of doing this yesterday
I tried the ordinary old "Import data from txt file"
Excel them remembers this import as a "connection" and you can set
properties of the connection
You can set it to refresh the data (ie - re-import) on open of the
spreadsheet, refresh every so many seconds, overwrite etc
So this is all good and simple except each time I click on the Refresh
button (in the Tool bar under the Data tab in excel 2007) the
navigation dialog box comes up, pointing to the text file and I have
to click import.
I even tried to record a macro : Import from txt navigate to text
file click import stop recording
I ran the macro and still I have to click that import button on the
dialog box. I want that to be just automatic.
This sheet will be used by others and I don't want them to have to
worry about the importing

Thanks- Hide quoted text -

- Show quoted text -

Thanx again for the macro

I ran it and it errored at
Sheets("hasdata").Select
it says subscript out of range

I commented out the line and reran
It emptied out all the data which is good then errored on
Let DataArray(Nbr, Y) = Left(L, InStr(L, ",") - 1)
it says invalid procedure call or argument


thanx