hometech
06-18-2013, 04:05 AM
Hello guys,
I have this challenge. I have two files in a folder, a word document and an access file.
Basically i want to read values into my word document form the access database file, both in the same folder.
I have been ale to achieve this by hard coding the path of the access database file and it works
Public Sub Time(control As IRibbonControl)
On Error GoTo Errhandler:
Dim valueRead As String
Dim strNum As String
Dim strText As String
valueRead = Application.Selection.Text
strNum = Val(Left(valueRead, InStr(valueRead, " ") - 1))
strText = Mid(valueRead, InStr(valueRead, vbTab) + 1)
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Set adoConn = New ADODB.Connection
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MS\contact.mdb"
.Open
End With
Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
.CommandText = "UPDATE time SET Comment = ('" & (strText) & "') where ID = " & strNum & " "
End With
adoCmd.Execute
adoConn.Close
Set adoConn = Nothing
MsgBox "Edit Successful"
Exit Sub
Errhandler:
MsgBox "Edit not successful"
End Sub
But i want it to read the file dynamically from the folder as i am not the only person that will be using the file and everyone has different PC environmental settings.
I want to use the Application.Path function to automatically locate the database file in the folder which it resides with the word document so that it can work anywhere it is placed on the computer and all the users will not have to start creating new folders and renaming files in their own C:\ drive (and what happend if they don't have a C:\ drive).
I tried using the code below but i keep getting the error "could not find the file"
Public Sub Time (control As IRibbonControl)
On Error GoTo Errhandler:
Dim valueRead As String
Dim strNum As String
Dim strText As String
Dim getfile As String
getfile = Application.ActiveDocument.path
valueRead = Application.Selection.Text
strNum = Val(Left(valueRead, InStr(valueRead, " ") - 1))
strText = Mid(valueRead, InStr(valueRead, vbTab) + 1)
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Set adoConn = New ADODB.Connection
With adoConn
.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=getfile\contact.mdb"
.Open
End With
Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
.CommandText = "UPDATE time SET Comment = ('" & (strText) & "') where ID = " & strNum & " "
End With
adoCmd.Execute
adoConn.Close
Set adoConn = Nothing
MsgBox "Edit Successful"
Exit Sub
Errhandler:
MsgBox "Edit not successful"
End Sub
Appreciate your suggestion on this guys, thanks....
I have this challenge. I have two files in a folder, a word document and an access file.
Basically i want to read values into my word document form the access database file, both in the same folder.
I have been ale to achieve this by hard coding the path of the access database file and it works
Public Sub Time(control As IRibbonControl)
On Error GoTo Errhandler:
Dim valueRead As String
Dim strNum As String
Dim strText As String
valueRead = Application.Selection.Text
strNum = Val(Left(valueRead, InStr(valueRead, " ") - 1))
strText = Mid(valueRead, InStr(valueRead, vbTab) + 1)
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Set adoConn = New ADODB.Connection
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MS\contact.mdb"
.Open
End With
Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
.CommandText = "UPDATE time SET Comment = ('" & (strText) & "') where ID = " & strNum & " "
End With
adoCmd.Execute
adoConn.Close
Set adoConn = Nothing
MsgBox "Edit Successful"
Exit Sub
Errhandler:
MsgBox "Edit not successful"
End Sub
But i want it to read the file dynamically from the folder as i am not the only person that will be using the file and everyone has different PC environmental settings.
I want to use the Application.Path function to automatically locate the database file in the folder which it resides with the word document so that it can work anywhere it is placed on the computer and all the users will not have to start creating new folders and renaming files in their own C:\ drive (and what happend if they don't have a C:\ drive).
I tried using the code below but i keep getting the error "could not find the file"
Public Sub Time (control As IRibbonControl)
On Error GoTo Errhandler:
Dim valueRead As String
Dim strNum As String
Dim strText As String
Dim getfile As String
getfile = Application.ActiveDocument.path
valueRead = Application.Selection.Text
strNum = Val(Left(valueRead, InStr(valueRead, " ") - 1))
strText = Mid(valueRead, InStr(valueRead, vbTab) + 1)
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Set adoConn = New ADODB.Connection
With adoConn
.ConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=getfile\contact.mdb"
.Open
End With
Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
.CommandText = "UPDATE time SET Comment = ('" & (strText) & "') where ID = " & strNum & " "
End With
adoCmd.Execute
adoConn.Close
Set adoConn = Nothing
MsgBox "Edit Successful"
Exit Sub
Errhandler:
MsgBox "Edit not successful"
End Sub
Appreciate your suggestion on this guys, thanks....