Free Microsoft Excel 2013 Quick Reference

Updating Records in .txt file

I want to update some records in a text file with data from a spreadsheet but there occurs an error in the '.Execute' statement.

I have no idea what the problem could be, here is the whole code, as you can see the updating function is linked to an event (clicking it) :

    Const adCmdText = &H1 
    Dim i As Long, j As Long 
    Dim MyWorkbook As Workbook 
    Dim datConnectiontxt As ADODB.Connection 
    Dim recSetxt As ADODB.Recordset 
    Dim strDBtxt, strSQLtxt, strSQLtxt2 As String 
     'I compare the data from the query against the data of a OWC Spreadsheet
    Dim spsUSheet As OWC10.Worksheet 
    Set spsUSheet = spsUsers.Worksheets(1) 
    Set MyWorkbook = ThisWorkbook 
    Set datConnectiontxt = New ADODB.Connection 
    Set recSetxt = New ADODB.Recordset 
     'Since this is an app for VBA (Excel), the Users.txt file (DataBase) is in the
     'same location
    strDBtxt = MyWorkbook.Path 
    strSQLtxt = "SELECT * FROM Users.txt WHERE [Description] = 'Desconocido';" 
    datConnectiontxt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strDBtxt & ";" & _ 
    "Extended Properties=""text;HDR=YES;FMT=FixedLenght""" 
    recSetxt.Open strSQLtxt, datConnectiontxt, adOpenForwardOnly, adLockReadOnly, adCmdText 
    For i = 2 To spsUSheet.Range("a1").End(xlDown).Row 
        If IsEmpty(spsUSheet.Cells(i, 2).Value) Or IsNull(spsUSheet.Cells(i, 2).Value) Goto Sig 
    End If 
     'In the line below occurs the error
    strSQLtxt2 = "UPDATE Users.txt SET [Description] = " & spsUSheet.Cells(i, 2).Value _ 
    & " WHERE [UserID] = " & spsUSheet.Cells(i, 1).Value & ";" 
    datConnectiontxt.Execute strSQLtxt2 
Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As always, I'd appreciate your help
Thanx in advance

Post your answer or comment

comments powered by Disqus
Hi all,

i am new to VBA programming kindly hlep me regarding this.

i have more than 2 lakh records in txt file
each record is like this
001JP 0010133 0001504612 02.05.1996 04:26:57
001JP 0005257 0001504655 02.05.1996 04:28:04
001GB 207103 0001508366 02.05.1996 12:46:21
001GB 203535 0001508449 02.05.1996 13:01:43
001JP 0010133 0001504612 02.04.1997 04:26:57
001JP 0005257 0001504655 02.06.1997 04:28:04
001JP 207103 0001508366 02.09.1997 12:46:21
001GB 203535 0001508449 02.05.1997 13:01:43
001GB 203535 0001508449 02.06.1997 22:01:03
001JP 207103 0001508366 02.02.1998 12:46:21
001GB 203535 0001508449 22.03.1998 13:01:43
001GB 203535 0001508449 25.04.1998 22:01:03

i want following details of the total count from the above data to be presented in excel.

JP 1996 Total no 2
1997 Total no 3
1998 Total no 1
GB 1996 Total no 2
1997 Total no 2
1998 Total no 1

JP means from the first record 001JP
GB means from the thrid record 001GB

JP and GB are country names.

i want to get total how many times it has been displayed in the txt file.

i am attaching the sample txt file and also the excel file for information.

kindly help me regarding this if possible with vba code or any example.

Thanks in advance,


I manually Copied and Pasted contains in TXT File.
The TXT file is as below.
It has only 1 Column


What I want is to run some VB Script so that anything after / is ignored
and the Output shold be reformatted as below

The Above O/p is anything aftyer / is ignore and the last character of the 1st field is A is replace with 0 and B is replace with 1.

Wouyld somebody throw some light how to get this scriptted


I need to update some fields in my excel sheet from a .txt file (comma or space separated), based on matching values of a key field in both files. I do not want to import the file into excel and do a vlookup.

I am thinking of a macro that prompts the user for the file location ( txt file) and then scans the txt file and updates the fields.

Would anyone know how to go about doing this or have any code I can use?


I use the tipical function:
Open file For Input As #1

to reading txt file line by line.

But instead to init the redaing from the first line...

1) I need to find, in position mid(line, 1,8), the string INIT0000 and strat the reading of next lines from here
2) stop the reading of line from file when the code found in mid(line, 1,8), the string INIT0001

please a fast method to find the first line because have perphs 2.300.000 of lines in original txt file

no possible duplicate string in position mid(line, 1,8)

Good morning, afternon all.

This is a plea to stop my boss claiming you can't trust computers!

I have written a long macro that checks fiels that 3 people used to do, and me and the macro can now do it all except this week (due to watching lots of world cup) a few errors slipped through the net. My boss has since decided he can't trust what he doesn't understand (COMPUTERS!!!).

The problem is only two things that get through the net.

The first and most crucial is when i import the txt file to excel (2000) it trims off the final space automatically (this later causes problem and therfore i need to be able to check for it). All of my lines in txt file are separated by "|" so the txt file line must finish with this or an integer 1-9.

The second prob is that when it converts txt to columns the date comes in as mm/dd/yy but when i go to change the format it claims it is in dd/mm/yy. Is there anything i can do? I have thought about changing excel to mm/dd/yy then importing and converting then changing back to dd/mm/yy, would this work?

I hate the fact that if i can't solve this my boss won't have faith in VBA that will save everyone here the insane job of checking all the text files amnually.

I know someone must be able to help me.

Hello everyone,

i have two different .txt files, (bir.txt) and (iki.txt) in my folder (c:)
many data in both (bir.txt) and (iki.txt) and (iki.txt) file was used as database...

First data in (bir.txt) is "asdf;123;0,25"

I want to find that and the following data into (iki.txt) file.

which macro code should i write to find the data in specific (.txt) files without using excel worksheet functions ?


I've got a spreadsheet that contains prices. The column is formatted to show just 2 decimal places, so in the cell you see 56.80, but in the formula bar it reads 56.7954545454546. For internal system reasons, I need to convert the file to a .txt file, but when I do, cells that end in 0 end up dropping the 0 (so this cell in question reads 56.8). How do I get the 0 to stay at the end?

I tried formatting the column that contains the pricing as text instead of a number, but then all the decimal places show up (56.7954545454546). So is there a way I can first convert 56.7954545454546 to 56.80, and then format it as text so that when I save the file as a .txt the trailing 0 doesn't get dropped? Thanks.


I need to compare records in two separate files and I'm looking for the best approach. It's no trouble to set up a routine to march down the first column in one file and set a few variables to the identifying values on the record, but when I move to the other file, what is the best way to find the corresponding record?

I also need to handle the case where the record is not found in the second file at all and continue the record by record comparison.

How can I remove duplicate records in an Excel file of names & addresses? I
have combined several lists from different sources. I think the main issue is
all caps vs upper & lower case.

An employee asked me if he had 3 files each containing the same database.
Is there anyway in which he can update the data by only keyboarding it into
one file and have it automatically update the rest of files?

Any help is appreciated.


Please help me with code that updates an existing record in database



I have an Excel file that I convert into a CSV file. The information in this
file is usually no more than 400 records. If I view the CSV file in Notepad,
sometimes the file contains 65,000+ records, which has my 400 records and
empty lines that contain commas only. But sometimes it only contains my 400
records. I need to figure out how to eliminate the extra records because I
have to import this into my payroll software.

Hi All,
New user here. I routinely create input files (.txt format) for a hydrology program written in fortran, and I was wondering if how difficult it would be to create VBA code that would automate that process, bearing in mind that I know almost nothing about VBA. I have some background in C++, but nothing too extensive. Essentially, I want the program to read multiple rows in excel, then extract data from multiple cells in each row and insert the data into multiple lines of text at specified locations (see example below). My guess is that someone has already done something similar, and I was wondering if anyone had a "goby" for code that I could edit for my particular purposes. I tried to search the forums (keywords: create .txt file), but I’m not sure if there are keywords more appropriate. Any help would be greatly appreciated.

Data to be read from .xls file:
[A1] [B1] [C1] [D1] [E1] [F1] [G1]
400 0.003 0.7 0.200 0.3942 SB100 0.211

Output to .txt file:
Kn=[D1] CR=[E1]
PER A=50 PER B=30 PER C=15 PER D=5

I was so confused when reading documents about updating record in the recordset using ADO. So please help me.
How can I change a record in the recordset and update it to the source data.
Thanks so much for your help.

An employee asked me if he had 3 files each containing the same database.
Is there anyway in which he can update the data by only keyboarding it into
one file and have it automatically update the rest of files?

Any help is appreciated.

Hello friends,

I use the following code to import all of the .txt files in a directory into the "Import" sheet of my workbook.

'Import files
    Dim myDir As String, fn As String, txt As String, n As Long
    Dim x, y, i As Long, ii As Long, flg As Boolean, a() As String
    Const delim As String = vbTab
    myDir = ThisWorkbook.Path & ""
    fn = Dir(myDir & "*.txt")
    Do While fn <> ""
        txt = CreateObject("Scripting.FileSystemObject") _
        .OpenTextFile(myDir & fn).ReadAll
        x = Split(txt, vbLf)
        ReDim a(1 To UBound(x) + 1, 1 To 1000)
        For i = IIf(flg, 1, 0) To UBound(x)
            y = Split(x(i), delim)
            n = n + 1
            For ii = 0 To UBound(y)
                a(n, ii + 1) = y(ii)
        Sheets("Import").Cells(Rows.Count, 1).End(xlUp)(2) _
        .resize(n, UBound(a, 2)).Value = a
        n = 0: flg = True
        fn = Dir

I run this process twice, using two different workbooks to import two different file types (both .txt, just containing different data sets) and I recently decided I would like to try and combine them into one operation. What I'd like to do is add an IF condition to my Import code such as:

IF the file name contains "confirm" THEN import to Sheets("Confirm") ELSE import to Sheets("Import")

For example, if my directory contains the following files:


The top three would go to the "Import" sheet and the bottom three would go to the "Confirm" sheet.

My VBA skills are, how do you say... developing, so any guidance is greatly appreciated.

Thank you.

Does anyone know or know where I can find out if this has been addressed in excel 2007.

Excel may add quotation marks automatically upon exporting to a txt file format.



    ' Range("A3:O10000").Select 
    ' Selection.ClearContents 
    'Aggiungi ' sotto 
    Do Until ActiveCell = "" 
        ActiveCell.Offset(1, 0).Select 
    'Fine attacca sotto 
    Close #1 
    Dim riga As String 
    Dim cont As Integer 
    Dim var_IMPP As String 
    Dim VAR_DATAP As String 
    Dim VAR_PARTP As String 
    Dim OPENfile As String 
    cont = 3 
    OPENfile = "E:EPFL0664AREA_132.EPF " 
    Open OPENfile For Input As #1 
    While Not EOF(1) 
        Line Input #1, riga 
        If Len(Trim(riga)) > 0 Then 
            If InStr(Mid(riga, 2, 24), "SPORTELLO :") > 0 Then 
                var_SPORT = Trim(Mid(riga, 15, 5)) 
                If Not EOF(1) Then 
                    Line Input #1, riga 
                    Line Input #1, riga 
                End If 
                If InStr(Mid(riga, 2, 24), "PARTITA N.:") > 0 Then 
                    VAR_PARTP = Trim(Mid(riga, 22, 8)) 
                    var_NOMIN = Trim(Mid(riga, 32, 30)) 
                End If 
                Line Input #1, riga 
                Line Input #1, riga 
                Line Input #1, riga 
                Line Input #1, riga 
                If InStr(Mid(riga, 4, 1), "/") > 0 Then 
                    var_CARIC = Trim(Mid(riga, 2, 10)) 
                    var_NUMEF = Trim(Mid(riga, 19, 10)) 
                    VAR_IMP = Trim(Mid(riga, 36, 14)) 
                    var_SCAD = Trim(Mid(riga, 53, 10)) 
                    var_BNL = Trim(Mid(riga, 67, 5)) 
                    var_CORR = Trim(Mid(riga, 79, 5)) 
                    var_DEB = Trim(Mid(riga, 94, 30)) 
                End If 
                Foglio1.Range("A" & Trim(Str(cont))).Value = VAR_PARTP 
                Foglio1.Range("B" & Trim(Str(cont))).Value = var_SPORT 
                Foglio1.Range("C" & Trim(Str(cont))).Value = var_NOMIN 
                Foglio1.Range("D" & Trim(Str(cont))).Value = var_CARIC 
                Foglio1.Range("E" & Trim(Str(cont))).Value = var_NUMEF 
                Foglio1.Range("F" & Trim(Str(cont))).Value = VAR_IMP 
                Foglio1.Range("G" & Trim(Str(cont))).Value = var_SCAD 
                Foglio1.Range("H" & Trim(Str(cont))).Value = var_BNL 
                Foglio1.Range("I" & Trim(Str(cont))).Value = var_CORR 
                Foglio1.Range("J" & Trim(Str(cont))).Value = var_DEB 
                cont = cont + 1 
            End If 
        End If 
    Close #1 
    'Call ORDINA 
    'Call DUPLICATI 
    'Call ORDINA2 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is a txt file:

i want from this from the macro:

but i have this:

difficult to go on nex rekord in txt file ...ARG!!!!!!!!!!!!!!!

Hi all,

i have a excel file and a txt file i want to anaylze the data from txt file.

i have two columns in txt file and they are country code and group.

i want the total different country codes and their total to be displayed in the excel sheet by clicking the button on sheet.

and aslo the percentage of the total.

kindly help me regarding this using vba code.

i am attaching two sample files kindly look into that and help me regarding this.

thanks in advance.


Hi !

How to save Excel sheet what contain five colums in txt-file with semicolon Excel VBA.

Colums must be different size examble column A must be 11, B must be 4 C must be 5, D must be 7 ,E8 character long ?

I have several txt-files containing data as "fixed-with-files". With
data in the positions 1 to 1900 on the top tree rows (not in the same
positions). What I want to do is to import the data into excel one
character per row (I know there is a limitation to 256 columns - that
is one of my problems) in order to check where the data is located and
if it is correct.

Since only 256 columns are available I can't import one character per
column from left to right but have to go from top to bottom one
character per row (the spaces are as important as the data). And I need
it to start over in the next column the next line in txt file starts.

Anyone who can help?


i have following problem. i have following data:
account amount description (usually inv. nos)
3456 55.66 334466
6785 122.12 45678, 98456

i concatenate it , add spaces so that all items are in fixed position, copy
and paste special values to another sheet and save this sheet as txt file.

problem is that some description fields contain commas and these lines in
txt file begin with " character. so txt file looks like this:

3456 55.66 334466
"6785 122.12 45678, 98456

wich makes second line 1 character longer. I need all lines to be the same
lenght. it only happens where there is comma in excel.
is there a way to go around it.



I was able to get some help a while ago and hoping for someone to reply as well. Any one have ideas how to create a userform which will be use for username and password input? I know how to create one but is there anyway that the username and password will be stored as .txt file instead of using worksheet to store the data?



I have a different datas in txt file. I want to create a worksheet with the
help of this txt file datas with the help of excel macro. My txt file doesn't
fit in excel worksheet becasue of the limitation of columns in excel. When I
retrieve my datas, it comes in three sheets. In my txt file, there is a
search condition to search a particular words and then display the
informations in worksheet which is comes under the search word. Is there
anybody how can help me to solve my this problem. I can send the sample of my
txt file and worksheet format if anybody interesting to solve my problem.

No luck finding an answer? You could always try Google.