CODEWITHSUNDEEP
excelvba
Comicwizard
Comicwizard

Reputation: 55

excel import text file using vba in access

I have a text file I need to import into excel from access. I have built the vba to produce the text file, place it in a folder, I have code to take my excel template and copy it.

but now to import the text file to a tab in my workbook I am having troubles with. I can manually import the text file in excel but if I could do the same thing in access I would have all my processes completely automated.

I have and idea and built out what I could but that part where it reads the text file I am drawing a blank on.

sub updatereport_Click()

Set rsquery1 =  ???

Set excelapp = CreateObject("Excel.application", "")
excelapp.Visible = True

Set XL = CreateObject("Excel.Application")

Set targetworkbook = excelapp.workbooks.Open(drive & Inputlocation & Inputcurrentsunday & "\" & "AgentSchedulesDOW-Verint_WkOf_" & Inputcurrentsunday & ".xlsx")

targetworkbook.worksheets("Data").Range("A2:BO45000").clearcontents
targetworkbook.worksheets("Data").Range("A2").copyfromrecordset rsquery1

targetworkbook.Save
targetworkbook.Close

XL.workbooks.Close
XL.Quit

excelapp.workbooks.Close

End Sub

Upvotes: 0

Views: 409

Answers (1)

JohnMacgillivray
JohnMacgillivray

Reputation: 96

Might this help? http://www.freevbcode.com/ShowCode.asp?ID=2180

With the combined code looking something like the following:

Sub updatereport_Click()

Dim connCSV As New ADODB.Connection
Dim rsquery1 As New ADODB.Recordset
Dim adcomm As New ADODB.Command
Dim path As String

path = "C:\Testdir\"  'Here Test dir is the Directory where
' the text file is located. don't write the file name here.

'This is connection for a text file without Header

 'connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
 & path & ";Extended Properties='text;HDR=NO;FMT=Delimited'"


'This is connection for a text file with Header (i.e., columns
 
connCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
& path & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"
    
    
   rsquery1.Open "Select * From test.txt", _
       connCSV, adOpenStatic, adLockReadOnly, adCmdText


Set excelapp = CreateObject("Excel.application", "")
excelapp.Visible = True

Set XL = CreateObject("Excel.Application")

Set targetworkbook = excelapp.Workbooks.Open(drive & Inputlocation & Inputcurrentsunday & "\" & "AgentSchedulesDOW-Verint_WkOf_" & Inputcurrentsunday & ".xlsx")

targetworkbook.Worksheets("Data").Range("A2:BO45000").ClearContents
targetworkbook.Worksheets("Data").Range("A2").CopyFromRecordset rsquery1

targetworkbook.Save
targetworkbook.Close

XL.Workbooks.Close
XL.Quit

It's possible you'd need to change more than the following, but definitely need to:

  1. put the path for your text file location in line 8
  2. update name of test file in line 23
  3. depending on whether your text file has a header or not may need to comment/uncomment the appropriate connCSV.open code

Upvotes: 1

Related Questions