Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 7th 08, 07:52 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1
Default Append Excel Sheet from constantly updating delimited text file

Here is the scenario.

I have a production machine that appends data to a delimited text file every
10 seconds. Every night, someone has to change the file name. The name they
change it to is never consistent. However, whatever they change it to in the
program, will begin appending data. The previous file will not be modified
again.

I need to write a script that will scan, every minute or so, for the current
file being modified and append the data to an existing Excel spreadsheet.

I have written a script that will scan one particular file name every 10
seconds and append the data that is being written to it to a named excel
sheet. The script is below. What I need is for the code to not look for the
specified file, in this case 1.txt, but any file in the directory being
currently modified.


strFile = "c:\control\1.txt"
intSeconds = 5
strDataDelimiter = " "
strExcelFile = "C:\control\data3.xls"
Const intForReading = 1
Const xlUp = -4162
Const xlInsertDeleteCells = 1
Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1
Set objFSO = CreateObject ("Scripting.FileSystemObject")

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
("SELECT * FROM __InstanceModificationEvent WITHIN " & intSeconds & " WHERE
" _
& "TargetInstance ISA 'CIM_DataFile' AND " _
& "TargetInstance.Name='" & Replace(strFile, "\", "\\") & "'")

Do
Set objLatestEvent = colMonitoredEvents.NextEvent


Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
objExcelApp.Workbooks.Open strExcelFile

objExcelApp.ActiveSheet.Range("A1").Select
On Error Resume Next
objExcelApp.Selection.QueryTable.Refresh False
' Check if no error was raised
If Err.Number = 0 Then
' MsgBox "Existing data refreshed."
' Otherwise if there was an error refreshing the querytable, do the
whole process again
Else
Err.Clear
On Error GoTo 0
objExcelApp.ActiveSheet.Cells.Delete

With
objExcelApp.ActiveSheet.QueryTables.Add("TEXT;c:\c ontrol\1.txt",
objExcelApp.ActiveSheet.Range("A1"))
.Name = "data3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = "<"
.TextFileOtherDelimiter = ":"
.TextFileOtherDelimiter = "\"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh False
End With
End If

objExcelApp.ActiveWorkbook.Save
objExcelApp.Quit
Set objExcelApp = Nothing

'MsgBox "Done"
Loop

  #2   Report Post  
Old April 8th 08, 09:33 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 857
Default Append Excel Sheet from constantly updating delimited text file

If the only file in the directory is a txt file then you might replace the
strFile = "C:\control\1.txt"
with
strFile = "C:\control\*.txt"

Cheers,
Shane Devenshire


"Ker G" wrote in message
...
Here is the scenario.

I have a production machine that appends data to a delimited text file
every
10 seconds. Every night, someone has to change the file name. The name
they
change it to is never consistent. However, whatever they change it to in
the
program, will begin appending data. The previous file will not be
modified
again.

I need to write a script that will scan, every minute or so, for the
current
file being modified and append the data to an existing Excel spreadsheet.

I have written a script that will scan one particular file name every 10
seconds and append the data that is being written to it to a named excel
sheet. The script is below. What I need is for the code to not look for
the
specified file, in this case 1.txt, but any file in the directory being
currently modified.


strFile = "c:\control\1.txt"
intSeconds = 5
strDataDelimiter = " "
strExcelFile = "C:\control\data3.xls"
Const intForReading = 1
Const xlUp = -4162
Const xlInsertDeleteCells = 1
Const xlDelimited = 1
Const xlTextQualifierDoubleQuote = 1
Set objFSO = CreateObject ("Scripting.FileSystemObject")

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
("SELECT * FROM __InstanceModificationEvent WITHIN " & intSeconds & "
WHERE
" _
& "TargetInstance ISA 'CIM_DataFile' AND " _
& "TargetInstance.Name='" & Replace(strFile, "\", "\\") & "'")

Do
Set objLatestEvent = colMonitoredEvents.NextEvent


Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
objExcelApp.Workbooks.Open strExcelFile

objExcelApp.ActiveSheet.Range("A1").Select
On Error Resume Next
objExcelApp.Selection.QueryTable.Refresh False
' Check if no error was raised
If Err.Number = 0 Then
' MsgBox "Existing data refreshed."
' Otherwise if there was an error refreshing the querytable, do the
whole process again
Else
Err.Clear
On Error GoTo 0
objExcelApp.ActiveSheet.Cells.Delete

With
objExcelApp.ActiveSheet.QueryTables.Add("TEXT;c:\c ontrol\1.txt",
objExcelApp.ActiveSheet.Range("A1"))
.Name = "data3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = "<"
.TextFileOtherDelimiter = ":"
.TextFileOtherDelimiter = "\"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh False
End With
End If

objExcelApp.ActiveWorkbook.Save
objExcelApp.Quit
Set objExcelApp = Nothing

'MsgBox "Done"
Loop




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Excel file to text delimited file Kiran Veeramallu[_2_] Excel Discussion (Misc queries) 3 May 1st 07 07:04 PM
Saving Excel sheet as a semicolon delimited file (.csv) e_dog95 Excel Discussion (Misc queries) 1 February 18th 06 11:02 PM
Append the data given in diff sheets of an Excel File to one sheet sansk_23 Excel Worksheet Functions 3 May 10th 05 02:00 AM
How can I save a file as a comma-delimited text file in Excel? LAM Excel Discussion (Misc queries) 1 May 3rd 05 10:24 PM
Export excel file to semicolon delimited text file capitan Excel Discussion (Misc queries) 5 April 7th 05 03:06 AM


All times are GMT +1. The time now is 12:14 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017