Leo_Polla_Psemata
Registered User.
- Local time
- Yesterday, 22:08
- Joined
- Mar 24, 2014
- Messages
- 364
Hi there, with the code i display below, we can export data from access to excel by using VBA.
In this link, someone can find the sample database.
I need to do the same with MS Word. I need to export data from Access to word.
At this moment I use the "Word merge" method and i get the desired result, however,
i must use and keep in a safe place this word template, if for some reason, someone removes this template,
then i must create a new one from scratch.
Could we create a word document with VBA ? No need of keeping objects outside access? (I wish all done in just one application only)
When we build the excel, we type the below and VBA knows where to stick the data, when Word ? Is there any way ?
.Range("A1").Value = "ID"
.Range("B1").Value = "BK"
In this link, someone can find the sample database.
Solved - Extract excel from access
@arnelgp This is a new thread, continued from this one https://www.access-programmers.co.uk/forums/threads/format-multiple-currencies-based-on-a-value.324037/ but since subject has changed, i open this new here. With the below code, we can retrieve from access tables, one report in excel. One...
www.access-programmers.co.uk
I need to do the same with MS Word. I need to export data from Access to word.
At this moment I use the "Word merge" method and i get the desired result, however,
i must use and keep in a safe place this word template, if for some reason, someone removes this template,
then i must create a new one from scratch.
Could we create a word document with VBA ? No need of keeping objects outside access? (I wish all done in just one application only)
When we build the excel, we type the below and VBA knows where to stick the data, when Word ? Is there any way ?
.Range("A1").Value = "ID"
.Range("B1").Value = "BK"
Code:
Private Sub Btn1_Click()
On Error GoTo SubError
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim SQL As String
Dim rsBS As DAO.Recordset
Dim i As Integer
'Show user work is being performed
DoCmd.Hourglass (True)
'*********************************************
' RETRIEVE DATA
'*********************************************
'SQL statement to retrieve data from database
SQL = "SELECT Forma1.IDM, Forma1.bk, Forma1.freight, Forma1.curre " & _
"FROM Forma1;"
'Execute query and populate recordset
Set rsBS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
'If no data, don't bother opening Excel, just quit
If rsBS.RecordCount = 0 Then
MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
GoTo SubExit
End If
'*********************************************
' BUILD SPREADSHEET
'*********************************************
'Create an instance of Excel and start building a spreadsheet
'Early Binding
Set xlApp = Excel.Application
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
Range("A4").Select
ActiveWindow.FreezePanes = True
' Here I try to remove grid
xlSheet.Activate
ActiveWindow.DisplayGridlines = False
With xlSheet
.Name = "IMPORT BLss"
.Cells.Font.Name = "Calibri"
.Cells.Font.Size = 10
'Format Labels
.Range("A1").Value = "ID"
.Range("B1").Value = "BK"
.Range("C1").Value = "FREIGHT"
.Range("D1").Value = "CURRENCY"
'provide initial value to row counter
i = 2
'Loop through recordset and copy data from recordset to sheet
Do While Not rsBS.EOF
.Range("A" & i).Value = Nz(rsBS!IDM, "")
.Range("B" & i).Value = Nz(rsBS!bk, "")
.Range("C" & i).Value = Nz(rsBS!freight, "")
.Range("D" & i).Value = Nz(rsBS!curre, "")
i = i + 1
rsBS.MoveNext
Loop
End With
SubExit:
On Error Resume Next
DoCmd.Hourglass False
xlApp.Visible = True
rsBS.Close
Set rsBS = Nothing
Exit Sub
SubError:
MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
"An error occurred"
GoTo SubExit
End Sub
