Export records into a text file based on criteria
 

I have an excel spreadsheet which imports data based on an MSQuery (refreshes
on open) which will provide a list of records.

What I want to do is this:

When a user updates a record, I would like Excel to export that complete
record into a central text file (comma delimited) on my network so we have a
log of what data was changed, time and who by.

Is this possible?

Export records into a text file based on criteria
 

Export CSV data is easy, the question how do you determine which cells havve
been updated. Does the querry produce a date on which cells were updated?

You could add a timestamp on you worksheet when the last update was
perfromed and then import from access only items that havve changed since the
last update.

The CSV file could contain on the first line a date stamp so you know which
records need to be exported.

You need a more complete plan to determine which records need to be exported.


Here is some sample code

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")




'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForAppending, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub



"Maver1ck666" wrote:

I have an excel spreadsheet which imports data based on an MSQuery (refreshes
on open) which will provide a list of records.

What I want to do is this:

When a user updates a record, I would like Excel to export that complete
record into a central text file (comma delimited) on my network so we have a
log of what data was changed, time and who by.

Is this possible?

Export records into a text file based on criteria
 

Thanks for the reply Joel.

I think the main problem will be that there will be over 50 of these files
being used and they all need to link back and update the same file.

The query doesn't timestamp anything. The data is imported into excel from
Access then the user makes any required changes.

"Joel" wrote:

Export CSV data is easy, the question how do you determine which cells havve
been updated. Does the querry produce a date on which cells were updated?

You could add a timestamp on you worksheet when the last update was
perfromed and then import from access only items that havve changed since the
last update.

The CSV file could contain on the first line a date stamp so you know which
records need to be exported.

You need a more complete plan to determine which records need to be exported.


Here is some sample code

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")




'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForAppending, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub



"Maver1ck666" wrote:

I have an excel spreadsheet which imports data based on an MSQuery (refreshes
on open) which will provide a list of records.

What I want to do is this:

When a user updates a record, I would like Excel to export that complete
record into a central text file (comma delimited) on my network so we have a
log of what data was changed, time and who by.

Is this possible?

Export records into a text file based on criteria
 

You are right. There is going to be a sharing problem. What happends if two
people open two differrent files at the same time? Both are going to attempt
to open the same file, but only one will get write permission. The other is
going to get READ-ONLY permission.

"Maver1ck666" wrote:

Thanks for the reply Joel.

I think the main problem will be that there will be over 50 of these files
being used and they all need to link back and update the same file.

The query doesn't timestamp anything. The data is imported into excel from
Access then the user makes any required changes.

"Joel" wrote:

Export CSV data is easy, the question how do you determine which cells havve
been updated. Does the querry produce a date on which cells were updated?

You could add a timestamp on you worksheet when the last update was
perfromed and then import from access only items that havve changed since the
last update.

The CSV file could contain on the first line a date stamp so you know which
records need to be exported.

You need a more complete plan to determine which records need to be exported.


Here is some sample code

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")




'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForAppending, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub



"Maver1ck666" wrote:

I have an excel spreadsheet which imports data based on an MSQuery (refreshes
on open) which will provide a list of records.

What I want to do is this:

When a user updates a record, I would like Excel to export that complete
record into a central text file (comma delimited) on my network so we have a
log of what data was changed, time and who by.

Is this possible?

Export records into a text file based on criteria
 

the plan is to have a delay when excel tries writing the file if the text
file is locked (I have some code in another spreadsheet which already does
this).

"Joel" wrote:

You are right. There is going to be a sharing problem. What happends if two
people open two differrent files at the same time? Both are going to attempt
to open the same file, but only one will get write permission. The other is
going to get READ-ONLY permission.

"Maver1ck666" wrote:

Thanks for the reply Joel.

I think the main problem will be that there will be over 50 of these files
being used and they all need to link back and update the same file.

The query doesn't timestamp anything. The data is imported into excel from
Access then the user makes any required changes.

"Joel" wrote:

Export CSV data is easy, the question how do you determine which cells havve
been updated. Does the querry produce a date on which cells were updated?

You could add a timestamp on you worksheet when the last update was
perfromed and then import from access only items that havve changed since the
last update.

The CSV file could contain on the first line a date stamp so you know which
records need to be exported.

You need a more complete plan to determine which records need to be exported.


Here is some sample code

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")




'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForAppending, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub



"Maver1ck666" wrote:

I have an excel spreadsheet which imports data based on an MSQuery (refreshes
on open) which will provide a list of records.

What I want to do is this:

When a user updates a record, I would like Excel to export that complete
record into a central text file (comma delimited) on my network so we have a
log of what data was changed, time and who by.

Is this possible?

Export records into a text file based on criteria
 

You can use the following function to get the user name


Name = Environ("USERNAME")

Are you going to use a Button to determine when a record is updated or use
an event like worksheet_update to determine when the record is updated. Do
you plan to keep the CSV file opened when the 2nd workbook is opened or do
you plan to keep on opening and closing the CSV file for each updated record?

Do you need any additional help with the coding?
"Maver1ck666" wrote:

the plan is to have a delay when excel tries writing the file if the text
file is locked (I have some code in another spreadsheet which already does
this).

"Joel" wrote:

You are right. There is going to be a sharing problem. What happends if two
people open two differrent files at the same time? Both are going to attempt
to open the same file, but only one will get write permission. The other is
going to get READ-ONLY permission.

"Maver1ck666" wrote:

Thanks for the reply Joel.

I think the main problem will be that there will be over 50 of these files
being used and they all need to link back and update the same file.

The query doesn't timestamp anything. The data is imported into excel from
Access then the user makes any required changes.

"Joel" wrote:

Export CSV data is easy, the question how do you determine which cells havve
been updated. Does the querry produce a date on which cells were updated?

You could add a timestamp on you worksheet when the last update was
perfromed and then import from access only items that havve changed since the
last update.

The CSV file could contain on the first line a date stamp so you know which
records need to be exported.

You need a more complete plan to determine which records need to be exported.


Here is some sample code

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")




'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForAppending, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub



"Maver1ck666" wrote:

I have an excel spreadsheet which imports data based on an MSQuery (refreshes
on open) which will provide a list of records.

What I want to do is this:

When a user updates a record, I would like Excel to export that complete
record into a central text file (comma delimited) on my network so we have a
log of what data was changed, time and who by.

Is this possible?

Export records into a text file based on criteria
 

Thanks for the environ code.

Not too sure how Excel would work out if the record has been updated or not?
Any suggestions on code would be a huge benefit.

The csv would have to be opened and closed I think in order to allow other
users changes that will need to be made.

Thanks again for your help, it is much appreciated ;)

Mav

"Joel" wrote:

You can use the following function to get the user name


Name = Environ("USERNAME")

Are you going to use a Button to determine when a record is updated or use
an event like worksheet_update to determine when the record is updated. Do
you plan to keep the CSV file opened when the 2nd workbook is opened or do
you plan to keep on opening and closing the CSV file for each updated record?

Do you need any additional help with the coding?
"Maver1ck666" wrote:

the plan is to have a delay when excel tries writing the file if the text
file is locked (I have some code in another spreadsheet which already does
this).

"Joel" wrote:

You are right. There is going to be a sharing problem. What happends if two
people open two differrent files at the same time? Both are going to attempt
to open the same file, but only one will get write permission. The other is
going to get READ-ONLY permission.

"Maver1ck666" wrote:

Thanks for the reply Joel.

I think the main problem will be that there will be over 50 of these files
being used and they all need to link back and update the same file.

The query doesn't timestamp anything. The data is imported into excel from
Access then the user makes any required changes.

"Joel" wrote:

Export CSV data is easy, the question how do you determine which cells havve
been updated. Does the querry produce a date on which cells were updated?

You could add a timestamp on you worksheet when the last update was
perfromed and then import from access only items that havve changed since the
last update.

The CSV file could contain on the first line a date stamp so you know which
records need to be exported.

You need a more complete plan to determine which records need to be exported.


Here is some sample code

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")




'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForAppending, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub



"Maver1ck666" wrote:

I have an excel spreadsheet which imports data based on an MSQuery (refreshes
on open) which will provide a list of records.

What I want to do is this:

When a user updates a record, I would like Excel to export that complete
record into a central text file (comma delimited) on my network so we have a
log of what data was changed, time and who by.

Is this possible?