?
Solved

How to run find and replace command in a text file from VBA?

Posted on 2015-01-04
7
Medium Priority
?
422 Views
Last Modified: 2015-01-05
Hi Experts,
I am interesting in a way to open from my Access app a text file and replace all commas, then save the document.
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 40530615
you can create  a new text file "myFile2.txt" without the commas

dim s as string
open "c:\folderName\myFile.txt" for input as #1
open "c:\folderName\myFile2.txt" for output as #2
do until eof(1)
line input #1, s
s=replace(s,",",":")           'change ":" with the replacement you want

print #2, s
loop
close #1
close #2
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40531906
Hi Ben,
I don't think you worded your question quite right.
You have a fixed width column text file that has some data that has commas in it.
This prevents you from importing the file directly as a csv file.

Rey has provided you with a solution that will replace all commas with colons and save the file with a new name.
Which is what you asked for in this Q

If you still remain interested in opening a fixed width column text file and processing the results line-by-line into fields and records, let me know.

Nick67
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40531978
...yes, please clarify:
"Replace" all commas with ...?
...or "Delete" all commas...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 4

Author Comment

by:bfuchs
ID: 40532175
@Ray,
Your solution works, just wonder if there is really a need to create a new document or this can be done in same document?

@Nick67,
For that I am happy with the solution provided in other thread, just wonder if what you had posted there could work for this as well?

@Jeff,
For what I am concerned now, it wouldn't matter, either way will work.

Thanks,
Ben
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 400 total points
ID: 40532201
just wonder if what you had posted there could work for this as well
Yes, the principle is pretty much universal.
You open the file, and can then proceed to read it line by line, and do whatever you would like with each line in VBA
Rey's solution here does the exact same things, but uses a different, less verbose, syntax than what I posted in your earlier question.

is really a need to create a new document
Both Rey's idea and mine require saving the read data to a new location.
But there is really very little involved with killing the source file and replacing it with the new file so in the end only the edited results exist
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1600 total points
ID: 40532210
<Your solution works, just wonder if there is really a need to create a new document or this can be done in same document?>

if you want to retain the same document..

after running my codes
kill/delete the original document with

kill  "c:\folderName\myFile.txt"

'then name the new file with the name of the old file

name  "c:\folderName\myFile2.txt" as  "c:\folderName\myFile.txt"
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40532241
Thanks Experts!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question