Text To Excel then to Access then to Excel

KenLim · October 6th, 2006, 03:15 AM

Hi,

I'm a beginner of VBA learner.
Actually i did some programming before this.
Now, i would to try to arrange the contents of my text files
into mc. excel & then procces & then import to mc. access as table & process again & finally export the results in mc. access into mc. excel as final step.

So, first step, i would like to learn how to process those text files into excel.

Can someone guide me on this?

Thanks.

kysonMok · May 25th, 2009, 01:17 AM

open new excel workbook, select Data>Get External Data>Import Text file,
select the text file, then separate every field with a line.
then paste it on a new worksheet.

gd luck~

SheriV · May 26th, 2009, 02:11 PM

First step: You can do this programatically using the following:

Code:

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;M:\DownLoads\TestFile.txt", Destination:=Range("A1"))
        .Name = "TestFile"         
.Refresh BackgroundQuery:=False
    End With

SheriV · May 26th, 2009, 02:27 PM

You can also use the code I gave you before to bring in the data then save the file as whatever you want. You can create a table directly in your database to store the data. You can create a delete query to delete out the data. Say you name it "DelDta". Then you can import your new data directly into the table. Your code would work as follows:

Code:

    
Set dbs = CreateObject("Access.Application")
    dbs.OpenCurrentDatabase "C:\Database\MyDatabase.mdb" (Whatever your database name is)
    dbs.Visible = True or dbs.visible = false (your preference)
    With dbs
             dbs.DoCmd.OpenQuery "DelDta", acNormal, acEdit (This is the qry to delete the existing data from the table)
            dbs.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"NewData"(This is the name of your table in access),
               "C:\TxtHlpr.xls"(this is the name of your file), True, "MyDatadb" (This is the name of the spreadsheet)
        End If
        dbs.CloseCurrentDatabase
    End With
    dbs.Quit

You have to ensure that the data in your spreadsheet is formated according to the formats you set up in your table.

Good Luck
S.