ActiveX Data Objects (ADO)

Active X Data Objects (ADO)

Add Below Library to use ADO

Option Explicit

Sub copyfromdb()
Dim salesconn As ADODB.Connection
Dim rst As ADODB.Recordset
Set salesconn = New ADODB.Connection
Set rst = New ADODB.Recordset
salesconn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\sid\Desktop\Movies.accdb;Persist Security Info=False"

salesconn.Open

With rst
.ActiveConnection = salesconn
.Source = "select region from sales"
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

Worksheets.Add
Range("A1").CopyFromRecordset rst.Fields
Range("A1").CopyFromRecordset rst
rst.Close

salesconn.Close

End Sub

File System Object (FSO)

FSO (File Sytstem Object)

Add Library to use early binding 

Option Explicit

Sub fso()
Dim fso As FileSystemObject
Dim name As String
Dim fldr As String
Dim rng As Range
Dim cell As Range
Dim ts As TextStream
Dim folder As folder
name = Format(Date, "mm-dd-yyyy")
fldr = "C:\Users\pc Admin\Desktop\" & name

Set fso = New FileSystemObject

If Not fso.FolderExists(fldr) Then
Set folder = fso.CreateFolder(fldr)
Else

End If
If Not fso.FileExists(fldr & "\My File.txt") Then
folder.CreateTextFile ("My File.txt")
End If
Set rng = Range("A1").CurrentRegion
Set ts = fso.OpenTextFile(fldr & "\My File.txt", ForWriting)

For Each cell In rng
ts.WriteLine cell
Next

End Sub