Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export access table to notepad
I'm after some help please. I'm trying though excel to open up access and
export a table to notepad. Having never done anything like this i'm struggling. I've mashed bits of code together that i've found on here but i'm still missing something. Can you look at the code below and point out where i'm going wrong or even suggest a better way? Sub access() Dim ac As Object On Error Resume Next Set ac = GetObject(, "Access.Application") If ac Is Nothing Then Set ac = GetObject("", "Access.Application") ac.OpenCurrentDatabase "k:\warehouse\lm's\test.mdb" Else AppActivate "Microsoft Access" End If DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" ac.Quit End Sub Many thanks, Richard. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export access table to notepad
Hi Richard,
Try the following:- Sub access() Dim ac As Object Const acExportDelim = 2 'Required with late binding On Error Resume Next 'Try GetObject first in case Access already open Set ac = GetObject(, "Access.Application") If Err.Number 0 Then 'Error is returned by GetObject if Access not 'already open so use CreateObject On Error GoTo 0 'Reset error trapping ASAP Set ac = CreateObject("Access.Application") End If ac.OpenCurrentDatabase "k:\warehouse\lm's\test.mdb" 'ac.Visible = True 'Can only use following line if Access is visible 'but there is no need to make it visible. 'AppActivate "Microsoft Access" ac.DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" ac.Quit Set ac = Nothing End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export access table to notepad
Thank you very much Ossie.
I noticed after I first posted that i'd missed the "ac." from the fron of DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" Thanks for your help and correcting my mashed code @:) "OssieMac" wrote: Hi Richard, Try the following:- Sub access() Dim ac As Object Const acExportDelim = 2 'Required with late binding On Error Resume Next 'Try GetObject first in case Access already open Set ac = GetObject(, "Access.Application") If Err.Number 0 Then 'Error is returned by GetObject if Access not 'already open so use CreateObject On Error GoTo 0 'Reset error trapping ASAP Set ac = CreateObject("Access.Application") End If ac.OpenCurrentDatabase "k:\warehouse\lm's\test.mdb" 'ac.Visible = True 'Can only use following line if Access is visible 'but there is no need to make it visible. 'AppActivate "Microsoft Access" ac.DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" ac.Quit Set ac = Nothing End Sub -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export access table to notepad
All of my code is now fully automated except for when access first loads when
i'm presented with the screen "do you want to....open, cancel etc" If I had this line in ac.OpenCurrentDatabase "K:\Operations\JEAN\Lean Pick Info\11. Nov\november.mdb;Persist Security Info=False" it disables the message but also prevents the exportation of the table to notepad - is there a way around this? Thanks, Richard. "Udd." wrote: Thank you very much Ossie. I noticed after I first posted that i'd missed the "ac." from the fron of DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" Thanks for your help and correcting my mashed code @:) "OssieMac" wrote: Hi Richard, Try the following:- Sub access() Dim ac As Object Const acExportDelim = 2 'Required with late binding On Error Resume Next 'Try GetObject first in case Access already open Set ac = GetObject(, "Access.Application") If Err.Number 0 Then 'Error is returned by GetObject if Access not 'already open so use CreateObject On Error GoTo 0 'Reset error trapping ASAP Set ac = CreateObject("Access.Application") End If ac.OpenCurrentDatabase "k:\warehouse\lm's\test.mdb" 'ac.Visible = True 'Can only use following line if Access is visible 'but there is no need to make it visible. 'AppActivate "Microsoft Access" ac.DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" ac.Quit Set ac = Nothing End Sub -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export access table to notepad
I don't get the message so I am not sure what it is all about. What version
of Access are you using? I tested it on Access 2002. I suggest that you post as a separate question and perhaps also on the Access Programming site. As an afterthought, try making Access visible (see my commented code in previous post) and then exit the sub and see whether the required database is in fact loaded. -- Regards, OssieMac "Udd." wrote: All of my code is now fully automated except for when access first loads when i'm presented with the screen "do you want to....open, cancel etc" If I had this line in ac.OpenCurrentDatabase "K:\Operations\JEAN\Lean Pick Info\11. Nov\november.mdb;Persist Security Info=False" it disables the message but also prevents the exportation of the table to notepad - is there a way around this? Thanks, Richard. "Udd." wrote: Thank you very much Ossie. I noticed after I first posted that i'd missed the "ac." from the fron of DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" Thanks for your help and correcting my mashed code @:) "OssieMac" wrote: Hi Richard, Try the following:- Sub access() Dim ac As Object Const acExportDelim = 2 'Required with late binding On Error Resume Next 'Try GetObject first in case Access already open Set ac = GetObject(, "Access.Application") If Err.Number 0 Then 'Error is returned by GetObject if Access not 'already open so use CreateObject On Error GoTo 0 'Reset error trapping ASAP Set ac = CreateObject("Access.Application") End If ac.OpenCurrentDatabase "k:\warehouse\lm's\test.mdb" 'ac.Visible = True 'Can only use following line if Access is visible 'but there is no need to make it visible. 'AppActivate "Microsoft Access" ac.DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt" ac.Quit Set ac = Nothing End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel worksheet export into access table | Excel Worksheet Functions | |||
Can you export a pivot table into Access? | Excel Discussion (Misc queries) | |||
Is there any way to export a .xls table to Access .mdb without Acc | Excel Discussion (Misc queries) | |||
Export Access Table to Excel | Excel Programming | |||
Export Data to Access Table | Excel Programming |