I'm trying to write a quick vb app to help replace server names in .ppx files. ppx files are Cognos PowerPlay files. The contain the server name that the .ppx is suppose to run against. We are a big PowerPlay shop. I'd like to write an access app which will allow them to identify the files to be changed..and the old server name and new server name. I can open and read the .ppx but when i try to write it to a table or do anything else with it I have problems because the data has " in it.
For instance, the first couple lines of data in the file looks like this:
<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<Document xmlns=" <HEAD xmlns="
Here is the code I have right now:
Public Sub RunCurrentRequest()
On Error GoTo RunCurrentRequest_Err
Dim objwPApp As Object
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strMsg As String
Dim strfullname As String
Dim objFileSystem
Dim txt As Object ' or scripting.textstream
Dim strLine As String
Dim strtablename As String
Set rst = New ADODB.Recordset
Set objFileSystem = CreateObject"Scripting.FileSystemObject")
strSQL = "select * from [tblPPXFiles]"
rst.Open strSQL, CurrentProject.Connection
gstrPPxFilePath = rst("Path")
gstrppxFileName = rst("filename")
While Not rst.EOF
gstrPPxFilePath = rst("Path")
gstrppxFileName = rst("filename")
strtablename = rst("tableName")
strfullname = gstrPPxFilePath & "\" & gstrppxFileName
strSQL = "CREATE TABLE " & strtablename & " (Textfield TEXT)"
DoCmd.RunSQL strSQL
Set txt = objFileSystem.opentextfile(strfullname, 1) ' 1 - ForReading
Do While Not txt.atendofstream
strLine = txt.readline
strSQL = "INSERT INTO " & strtablename & "(Textfield) values ('" & replace(strline, "#"#","^") & "')"
DoCmd.RunSQL strSQL
Loop
txt.Close
Set txt = Nothing
If Err <> 0 Then
MsgBox "Unable to Open WordPad file -" & strfullname
End If
rst.MoveNext
Wend
For instance, the first couple lines of data in the file looks like this:
<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<Document xmlns=" <HEAD xmlns="
Here is the code I have right now:
Public Sub RunCurrentRequest()
On Error GoTo RunCurrentRequest_Err
Dim objwPApp As Object
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strMsg As String
Dim strfullname As String
Dim objFileSystem
Dim txt As Object ' or scripting.textstream
Dim strLine As String
Dim strtablename As String
Set rst = New ADODB.Recordset
Set objFileSystem = CreateObject"Scripting.FileSystemObject")
strSQL = "select * from [tblPPXFiles]"
rst.Open strSQL, CurrentProject.Connection
gstrPPxFilePath = rst("Path")
gstrppxFileName = rst("filename")
While Not rst.EOF
gstrPPxFilePath = rst("Path")
gstrppxFileName = rst("filename")
strtablename = rst("tableName")
strfullname = gstrPPxFilePath & "\" & gstrppxFileName
strSQL = "CREATE TABLE " & strtablename & " (Textfield TEXT)"
DoCmd.RunSQL strSQL
Set txt = objFileSystem.opentextfile(strfullname, 1) ' 1 - ForReading
Do While Not txt.atendofstream
strLine = txt.readline
strSQL = "INSERT INTO " & strtablename & "(Textfield) values ('" & replace(strline, "#"#","^") & "')"
DoCmd.RunSQL strSQL
Loop
txt.Close
Set txt = Nothing
If Err <> 0 Then
MsgBox "Unable to Open WordPad file -" & strfullname
End If
rst.MoveNext
Wend
