VBA - Read a .TXT file and manipulate its lines

Question

VBA - Read a .TXT file and manipulate its lines

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I'm trying to make a Query generator using Excel (VBA), but I'm not able to manipulate some data inside a .sql file.

Eg:

Select *
From Tabela t
Where t.id = 1
<PROP>

Whenever he finds a line that is written PROP he should swap for the value in cell H3, and at the end save the file.

I can already read the file to the end, but I can not change the PROP value by the value in the H3 cell.

To get better explained.

Dim strTextLine
Dim strTextFile
Dim intFileNumber

'Nome completo do arquivo a ser aberto
strTextFile = "C:\Users\Desktop\Query.sql"

'Criar numeração
intFileNumber = 1

'Criar conexão com o arquivo txt
Open strTextFile For Input As #intFileNumber  '<- Input ou Append?

'Loop para percorrer as linhas do arquivo até o seu final
Do While Not EOF(intFileNumber)
   Line Input #intFileNumber, strTextLine
   If (strTextLine = "<PROP>") Then
       'como faço para alterar a tag <PROP> por um valor que está em uma variavel?
       ' Já consegui encontrar a linha só não sei como altero o valor no arquivo.
    End If
Loop

'Fechar a conexão com o arquivo
Close #intFileNumber

Sincerely,

excel vba excel-vba

asked by anonymous 01.10.2018 / 17:55

2 answers

RegExp with only one letter With limiting the result of Select in Oracle

score 1 · Answer 1

As I can not comment, I leave here a suggestion that I think can help you.

Columns("B:B").Select
    Selection.Replace What:="PROP", Replacement:=Range("H1").Value, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Define the columns where the text has to be located and run the macro.

score 1 · Answer 2

I'm assuming the .sql file already exists. Adjust the code as needed.

Option Explicit

Sub SqlMacro()
Dim ficheiro As String
Dim texto As String
Dim textline As String
Dim celula As String

    On Error GoTo Erro

    'localização do ficheiro
    ficheiro = "C:\teste.sql"

    'abre o ficheiro
    Open ficheiro For Input As #1

    'lê o ficheiro
    Do Until EOF(1)
        Line Input #1, textline
        texto = texto & textline
    Loop
    'fecha o ficheiro
    Close #1

    'adquire o conteúdo da célula H3, na folha 1
    celula = Sheets(1).Cells(3, 8)  '(linha, coluna)
    'faz a troca de <PROP> pelo conteúdo da célula
    texto = Replace(texto, "<PROP>", celula)

    'sobrescreve o ficheiro
    Open ficheiro For Output As #1
    Print #1, texto
    Close #1
    Exit Sub

Erro:
    'se houver algum erro, descreve-o
    MsgBox Err.Description
End Sub