#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
See it this helps: http://gregmaxey.com/word_tip_pages/...rom_forms.html
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 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 |