by Sam Blagoslovenskiy (5 Submissions)
Category: Files/File Controls/Input/Output
Compatability: Visual Basic 3.0
Difficulty: Unknown Difficulty
Originally Published: Fri 1st November 2002
Date Added: Mon 8th February 2021
Rating: (1 Votes)
Export data from Access Database to Excel
Dim myEx As Object
Set myEx = CreateObject("Excel.Application")
Set myWorbook = myEx.workbooks.Add()
Set myWorkseet = myWorbook.worksheets(1)
myWorkseet.Range("A1").Select
With myWorkseet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=<databasefilenamename>;DriverId=25;FIL=MS Access;MaxBufferSize=" _
), Array("2048;PageTimeout=5;")), Destination:=myWorkseet.Range("A1"))
.CommandText = Array( _
" select * FROM `<databasefilenamename tablename>" _
)
.Name = "LCMS09292001"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
myEx.Visible = True
Set myWorkseet = Nothing
Set myWorbook = Nothing
Set myEx = Nothing
End Sub