Problem Exporting with "#" in field name (1 Viewer)

[PatrickJohnson]

I am using access to generate a payroll file for upload into our enterprise system. the enterprise system requires a field called "File #" and the pound sign is a required part of the field. everything is fine until i try to export the actual file. when i export the file, this field comes across as "File ." instead. It is replacing the pound sign with a period. how can i stop this? i've tried changing the export language, using "" as text delimiters, but nothing works. thoughts?

 

[boblarson]

Two thoughts...

1. change the field name to FileNum instead of # in the table and then create a query and create the field name of File #:FileNum like that. Then see if it will export properly.

2. If that doesn't help then change the field to FileNum and then after exporting, use Code to open the file and replace FileNum with File #.

 

[PatrickJohnson]

Two thoughts...

1. change the field name to FileNum instead of # in the table and then create a query and create the field name of File #:FileNum like that. Then see if it will export properly.

2. If that doesn't help then change the field to FileNum and then after exporting, use Code to open the file and replace FileNum with File #.

yeah, i tried number 1 already, no dice. i would like to try number 2 but i can't seem to get in my head exactly how to do that. i'd like it to just be a line in my code that either manually inserts my header row or that does a search and replace on the header row. thoughts?

 

[macca the hacke]

what type of file do you export - csv, excel, txt?

 

[PatrickJohnson]

what type of file do you export - csv, excel, txt?

csv file, also a requirement

 

[macca the hacke]

Here is a link to a good article on how to automate excel from access - I have used it several times and is really good (I am assuming here that the csv files you export anre small enough to handle through excel - if not would have to open through notepad, but that is no problem):

http://www.mvps.org/access/modules/mdl0006.htm

 

[PatrickJohnson]

Here is a link to a good article on how to automate excel from access - I have used it several times and is really good (I am assuming here that the csv files you export anre small enough to handle through excel - if not would have to open through notepad, but that is no problem):

http://www.mvps.org/access/modules/mdl0006.htm

many of the entries have leading zeroes that are required, which as we all know will get killed in excel. interested in automating notepad though. is there a way to make the edit without actually fully opening the file?

 

[macca the hacke]

Try this code:

** code start**

Dim objFSO As New FileSystemObject
Dim objFile As Scripting.TextStream
Dim strContents

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("your csv file including location", ForReading)

strContents = objFile.ReadAll
objFile.Close

Set objFile = objFSO.OpenTextFile("your csv file including location", ForWriting)

strContents = Replace(strContents, "FileNum ", "File # ")
objFile.Close

** code ends **

What this is actually doing is opening text file, reading contents into variable strContents, and then opening text file to write contents having replaced FileNum with File #

Obvious downside is if FileNum appears in places where you don't want it changed

 

[PatrickJohnson]

Try this code:

** code start**

Dim objFSO As New FileSystemObject
Dim objFile As Scripting.TextStream
Dim strContents

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("your csv file including location", ForReading)

strContents = objFile.ReadAll
objFile.Close

Set objFile = objFSO.OpenTextFile("your csv file including location", ForWriting)

strContents = Replace(strContents, "FileNum ", "File # ")
objFile.Close

** code ends **

What this is actually doing is opening text file, reading contents into variable strContents, and then opening text file to write contents having replaced FileNum with File #

Obvious downside is if FileNum appears in places where you don't want it changed

Thanks for your help! I'm getting a compile error on the declaration of objFSO AS New FileSystemObject. It is saying User-defined type not defined.

 

[boblarson]

If you are using objFSO AS New FileSystemObject then you need to set a reference to the Microsoft Scripting Runtime. However, if you use the

Set objFSO = CreateObject("Scripting.FileSystemObject")


instead, as shown in the code, you don't.

 

[PatrickJohnson]

If you are using objFSO AS New FileSystemObject then you need to set a reference to the Microsoft Scripting Runtime. However, if you use the

Set objFSO = CreateObject("Scripting.FileSystemObject")


instead, as shown in the code, you don't.

the code shows both. so i should remove the declaration?

EDIT: jsut removed that declaration, then got a compile error on the DIM objFile as Scripting.TextStream declaration

 

[boblarson]

Change these:

Dim objFSO As New FileSystemObject
Dim objFile As Scripting.TextStream


To

Dim objFSO As Object
Dim objFile As Object

 

[PatrickJohnson]

Change these:

Dim objFSO As New FileSystemObject
Dim objFile As Scripting.TextStream


To

Dim objFSO As Object
Dim objFile As Object

Thanks, that stopped the error. Now my only issue is that the solution isn't working. I still get "File ." instead of "File #" on my file. here is the code:

**CODE**

Private Sub Command2_Click()
Dim periodend As Date
Dim strperiod As String
Dim objFSO As Object
Dim objFile As Object
Dim strContents

Const ForReading = 1
Const ForWriting = 2

'Make temporary data table of time entry data
DoCmd.OpenQuery "Epip"
'User enters period end date, old file backed up with old period date in filename
periodend = CDate(InputBox("Enter Period End Date (mm/dd/yyyy)", "Pay Period")) - 14
'Reformat date for file naming purposes to yyymmdd format
strperiod = Format(Year(periodend), "0000") & Format(Month(periodend), "00") & Format(Day(periodend), "00")
'backup old data before overwrite
FileCopy "J:\apps\timesaver\impexp\gh6.csv", "J:\apps\timesaver\impexp\previous csv files\" & strperiod & "-gh6.csv"
'Export new data to csv file overwriting old file
DoCmd.TransferText acExportDelim, "GH6Export", "EpipTemp", "J:\apps\timesaver\impexp\gh6.csv", True
'Open generated csv file and replace "File ." with "File #" for loading into enterprise
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\previous csv files\" & strperiod & "-gh6.csv", ForReading)
strContents = objFile.ReadAll
objFile.Close
Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\previous csv files\" & strperiod & "-gh6.csv", ForWriting)
strContents = Replace(strContents, "File .", "File # ")
objFile.Close


End Sub

 

[macca the hacke]

Thanks, that stopped the error. Now my only issue is that the solution isn't working. I still get "File ." instead of "File #" on my file. here is the code:

If that code is copied exactly from your code, then have you tried changing

Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\prev ious csv files\" & strperiod & "-gh6.csv", ForReading)

to

Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\previous csv files\" & strperiod & "-gh6.csv", ForReading)

ie - taking out the space that you have in the folder name that you haven't got earlier in the code. There is the same problem further down when you open the text file again

 

[PatrickJohnson]

If that code is copied exactly from your code, then have you tried changing

Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\prev ious csv files\" & strperiod & "-gh6.csv", ForReading)

to

Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\previous csv files\" & strperiod & "-gh6.csv", ForReading)

ie - taking out the space that you have in the folder name that you haven't got earlier in the code. There is the same problem further down when you open the text file again

that seems to be a problem with the copy paste formatting, the spaces in "previous" are not there in the actual code.

 

[PatrickJohnson]

Okay, the following code works it looks like except for that the part to write the contents back to the file seems to be missing:

Private Sub Command2_Click()
Dim periodend As Date
Dim strperiod As String
Dim objFSO As Object
Dim objFile As Object
Dim strContents As String

Const ForReading = 1
Const ForWriting = 2
DoCmd.OpenQuery "Epip" 'makes table to export
periodend = CDate(InputBox("Enter Period End Date (mm/dd/yyyy)", "Pay Period")) - 14 'sets date for backup filename
strperiod = Format(Year(periodend), "0000") & Format(Month(periodend), "00") & Format(Day(periodend), "00") 'reformats date to yyyymmdd
FileCopy "J:\apps\timesaver\impexp\gh6.csv", "J:\apps\timesaver\impexp\previous csv files\GH6\" & strperiod & "-gh6.csv" 'backs up old file before overwrite
DoCmd.TransferText acExportDelim, "GH6Export", "EpipTemp", "J:\apps\timesaver\impexp\Current Payroll Load Files\GH6\gh6.csv", True 'exports new file
'routine to replace "File ." with "File #"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\Current Payroll Load Files\GH6\gh6.csv", ForReading)
strContents = objFile.ReadAll
objFile.Close
Set objFile = objFSO.OpenTextFile("J:\apps\timesaver\impexp\Current Payroll Load Files\GH6\gh6.csv", ForWriting)
strContents = Replace(strContents, "File .", "File # ")
objFile.Close

End Sub

this renders a blank file, and i think there should be something between the las two lines that writes the contents of strContents to the file. Could any of you enlighten me on this?

 

[PatrickJohnson]

bumpety bump!

 

[georgedwilkinson]

Add:

objFile.Write strContents

after the replace and before the last close...right where you said it should be.

 

[PatrickJohnson]

Add:

objFile.Write strContents

after the replace and before the last close...right where you said it should be.

AWESOME!!
Thanks everyone, with all your help it finally works!

 

[georgedwilkinson]

NP. Glad we could help.