how to format a Docmd to pull in an Access file in Text format
 

I have an Access db that is running linked tables. The tables are too
large to kick out from Access to Excel. I thought the solution should
then be, to create a form in Excel that will tell Excel to grab the
table and put it into Excel.

First I created a macro:

Sub Access_grab_test_2()
'
' Access_grab_test_2 Macro
' Macro recorded 11/2/2005 by KP_User
'

'
ChDir "F:\EAP & EDG Master Files DO NOT MOVE\TRANSPORT GENERATOR"
Workbooks.OpenDatabase Filename:= _
"F:\EAP & EDG Master Files DO NOT MOVE\TRANSPORT
GENERATOR\TRANSPORT GENERATOR.mdb" _
, CommandText:=Array("tblEAP_AMB_WEEKLY_GENERATED"),
CommandType:= _
xlCmdTable
End Sub

this creates the table, but not in Text, so then I tried:


Sub Access_grab_test()
'
' Access_grab_test Macro
' Macro recorded 11/2/2005 by KP_User
'

'
Workbooks.Add
Cells.Select
Selection.NumberFormat = "@"
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:= _
False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
ColorIndex = 15
Pattern = xlSolid
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Font.Bold = True
Windows("Book1").Activate
End Sub

but this won't open the db without the first bit of code. How do I
combine these so that I can open Access like the first macro, but then
have the table pull into excel text formatted?

HELP???


--
Campbellj4
------------------------------------------------------------------------
Campbellj4's Profile: http://www.excelforum.com/member.php...o&userid=28506
View this thread: http://www.excelforum.com/showthread...hreadid=481005