Save and close a file in notepad

[Antigoon]

Since migrating to Access 2010 a macro that we use to export contents of a table to a text file has been giving problems.

In a certain field when it is left empty instead of blanks it puts NUL --> this gives problems when that text file is read in SAP.

The problem is resolved if the text file is opened and then saved in notepad so i tried to do this in the macro.

The code to open the text file in notepad is easy and given below but so far i have not found a way to save and close the file automatically.

Is there a way do do this in vba ?

Thank you in advance for all help, Stefan.

Dim myFile As String
Dim Filename As String
Filename = Format(Date, "yyyymmdd") + "Sap.txt"

myFile = Shell("C:\WINDOWS\notepad.exe \\gentlmxlm\data5\KAS\Jeugddienst\" + Filename, vbNormalFocus)

 

[mdlueck]

Since migrating to Access 2010 a macro that we use to export contents of a table to a text file has been giving problems.

In a certain field when it is left empty instead of blanks it puts NUL --> this gives problems when that text file is read in SAP.

Would suggest you write your export logic in VBA rather than rely on the correct default behavior of Access Export ala Macro. Then you could use the Nz() function to convert NULL to what ever value you would like.

About driving Notepad... then you would need to check into OLE Automation commands of Notepad, which I have no idea how automated Notepad is. Sounds very hokey to me. Better to just roll your own export code in VBA.

 

[boblarson]

better to just roll your own export code in vba.

^^^^^^^^^^ +1 ^^^^^^^^^^^^