Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Data into an Excel sheet-template
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Data into an Excel sheet-template
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Data into an Excel sheet-template
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Data into an Excel sheet-template
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Data into an Excel sheet-template
On Jun 26, 8:23*am, spacemancw wrote:
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- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Data into an Excel sheet-template
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data from Excel worksheet into Excel template | Excel Discussion (Misc queries) | |||
How to merge data in Excel - one sheet to template sheet? | Excel Worksheet Functions | |||
Importing data into specific template cells | Excel Discussion (Misc queries) | |||
Problem with macro for importing data to template | Excel Programming | |||
Importing CSV data into a template or? | Excel Worksheet Functions |