Hi,
It's possible, in various different ways - though there are probably
better ways of tackling the problem. In general, you should think in
terms of replacing your memo-field-containing-the-contents-of-a-CSV-file
with a related table in which each record contains one row of CSV, with
a foreign key linking it to the parent record in the main table. You can
then manipulate the data in Access without bothering with Excel.
This approach (with modifications) will work even if the structure of
the related data varies between records in the main table.
But if for other reasons you have to store the data as text in memo
fields, the following should help. I've pasted a function at the end of
this message that writes the contents of a variable to a textfile, and
another that reads a textfile back into a variable. Both can be used in
Access queries.
This is a Microsoft article about moving data from Visual Basic to
Excel; most of it applies to Access VBA:
http://support.microsoft.com/default.aspx?scid=kb;en-us;247412
These links may also be useful: they are about controlling Excel from
Access:
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm
Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859
ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148)
http://support.microsoft.com/?id=210148
ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476
Function WriteToFile(Var As Variant, _
FileSpec As String, _
Optional Overwrite As Long = True) _
As Long
'Writes Var to a textfile as a string.
'Returns 0 if successful, an errorcode if not.
'Overwrite argument controls what happens
'if the target file already exists:
' -1 or True (default): overwrite it.
' 0 or False: append to it
' Any other value: abort.
Dim lngFN As Long
On Error GoTo Err_WriteToFile
lngFN = FreeFile()
'Change Output in next line to Append to
'append to existing file instead of overwriting
Select Case Overwrite
Case True
Open FileSpec For Output As #lngFN
Case False
Open FileSpec For Append As #lngFN
Case Else
If Len(Dir(FileSpec)) > 0 Then
Err.Raise 58 'File already exists
Else
Open FileSpec For Output As #lngFN
End If
End Select
Print #lngFN, CStr(Nz(Var, ""));
Close #lngFN
WriteToFile = 0
Exit Function
Err_WriteToFile:
WriteToFile = Err.Number
End Function
Function FileContents(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument
Dim lngFN As Long
On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents = Null
Else
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents = Input(LOF(lngFN), #lngFN)
End If
ErrCode = 0
GoTo Exit_FileContents
Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
FileContents = CVErr(Err.Number)
Else
FileContents = Null
End If
Err.Clear
Exit_FileContents:
Close #lngFN
End Function