Importing Data into an Excel sheet-template

[spacemancw]

I have a linux shell script that outputs data to a text file.
I can name that file anything, xxx.csv or xxx.txt
I can delimit with tabs or commas, whatever necessary.
I then ftp the file to a windows box where I have MS Excel 2007.

I want to have a sort of dynamic spreadsheet that depends on the data
in that file. That file will always exist on the PC in the same static
path.

The file gets updated daily. The data for some cells could change or
there could be new rows. (Not new columns - new columns would mean I
would have to add a column to my spreadsheet).

So I want to create a spreadsheet that has all the colum headers, all
the column and row spacing, colors, fonts etc. Then I want to import
the new data daily (or actually when I open the sheet) and overwrite
the existing data. This could be a macro (I guess it has to be a
macro). Maybe something that would launch on open, or even if I just
added a macro button to the sheet it would be fine.

So the data would start say in cell A4. There are 10 columns, so it
would stretch to J4
and there could be 300 rows one day, 302 the next and even 298 the
next day.
I want to make sure the old data is gone and only the new data
displays.


Thanks

 

[Mike H.]

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

 

[spacemancw]

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

 

[spacemancw]

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

 

[spacemancw]

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- Hide quoted text -

- Show quoted text -

BTW, the answer to my other issue about clicking the import button,
the External Data Range Properties has a check box for "Prompt for
file name on refresh" I unchecked that and it works great

 

[Mike H.]

If you get that message,

it says invalid procedure call or argument

then your data is not comma-delimited with ten parts. That is what I set
the macro to run expected. So if your data has no commas separating the 10
elements in a row, then you'll get this error.