Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-12-2015, 07:48 AM
pboland pboland is offline Export Word Form Data to Access Windows 7 64bit Export Word Form Data to Access Office 2013
Novice
Export Word Form Data to Access
 
Join Date: Jun 2015
Posts: 1
pboland is on a distinguished road
Default Export Word Form Data to Access

I'm trying to export data from some forms that I get set sent to be and send it to an Access Database or Excel spreadsheet (which ever is easier).

I've found some code that I'm using to test whether it can be done and so far I don't seem to getting anywhere. All I seem to get is the message:
"The selected folder did not contain any forms to process", even though there are 3 files in the folder containing data.

Here is the code that I've input so far:

Sub TallyDataInDataBase()


'Extract data from document form fields and store in Access database
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library or later
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document
Dim FiletoKill As String
'Call function to get path to saved forms
oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Call function to create a processed forms folder
CreateProcessedDirectory oPath
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 10000) 'User a number larger the expected number of files to process
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
If i = 0 Then
MsgBox "The selected folder did not contain any forms to process."
Exit Sub
End If
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string.
vConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=S:\SH_AZ_MM_Mentoring\Tally\Tally Data.accdb;"

'NOTE if using an ".accdb" format data base use the following connection string:
'vConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=D:\Batch\Tally Data Forms\Tally Data.accdb;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), Visible:=False)
FiletoKill = oPath & myDoc 'Identify the file to move after processing
vRecordSet.AddNew
With myDoc
If .ContentControl("Name").Result <> "" Then _
vRecordSet("Name") = .ContentControl("Name").Result
If .ContentControl("FavFood").Result <> "" Then _
vRecordSet("FavFood") = .ContentControl("FavFood").Result
If .ContentControl("FavColor").Result <> "" Then _
vRecordSet("FavColor") = .ContentControl("FavColor").Result
.SaveAs oPath & "Processed\" & .Name 'Save processed file in Processed folder
.Close
'File as been saved in the processed file folder. Delete it from the batch folder
Kill FiletoKill
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
lbl_Exit:
Exit Sub
End Sub
Reply With Quote
  #2  
Old 06-12-2015, 06:53 PM
gmaxey gmaxey is offline Export Word Form Data to Access Windows 7 32bit Export Word Form Data to Access Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

See it this helps: http://gregmaxey.com/word_tip_pages/...rom_forms.html
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Word Form Data to Access Export data from C# winForms treeView to MS-Word MergeFields nisim651 Mail Merge 8 04-18-2014 04:17 AM
Export VBA form Data to CSV rhys.downard Outlook 0 02-29-2012 07:35 AM
Export Word Form Data to Access Export Data from Ms project to Ms access using VBA virencm Project 4 02-08-2012 03:18 PM
Updating Access with Word Form b2995 Word VBA 0 10-16-2011 09:37 PM
Create a Custome Form and export data to Access ashleybyrdnc Office 0 03-05-2010 09:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft