Absolutely:
Code:
Option Compare Database
Public Sub ConvertCSVtoXL(strCSVPath As String)
Dim appExcel As Excel.Application
'Switch to Microsoft Excel so it won't go away when you finish.
On Error Resume Next
AppActivate "Microsoft Excel"
'If Excel isn't running, start and activate it
If Err Then
Shell "c:\Program Files\Microsoft Office\Office\" _
& "Excel /Automation", vbHide
AppActivate "Microsoft Excel"
End If
On Error GoTo 0
'Get an Application object so you can automate Excel.
Set appExcel = GetObject(, "Excel.Application")
With appExcel
.Workbooks.Open FileName:=strCSVPath
ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xlsm" _
, FileFormat:=52
End With
appExcel.Quit
Set appExcel = Nothing
MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
"filename with an XLSM extension"
End Sub
'------------------------------------------------------------
' Macro11
'
'------------------------------------------------------------
Function Macro11()
On Error GoTo Macro11_Err
'Call AddNew
ConvertCSVtoXL "C:\PMI\temp1.csv"
Macro11_Exit:
Exit Function
Macro11_Err:
MsgBox Error$
Resume Macro11_Exit
End Function
1) Macro1 uses the above code to convert the CSV file to a XLS file (by calling Macro11())
2) Macro1 links the resulting file (temp1.xls) to a table named "temp" in Access
3) Macro1 runs a query
4) Macro1 will then save the file in an Excel format, open the file in Excel and begin an Excel macro (this step not done yet)
hopefully this makes sense
as for those last two errors (""The remote server machine does not exist or is unavailable" and ""ActiveX component can't create object") these are separate issues AFAIK that i didn't have at work but have at home, so I'm somewhat willing to overlook them. But it negates my ability to test at home.