View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Campbellj4[_2_] Campbellj4[_2_] is offline
external usenet poster
 
Posts: 1
Default 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 to
large to kick out from Access to Excel. I thought the solution shoul
then be, to create a form in Excel that will tell Excel to grab th
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\TRANSPOR
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
combine these so that I can open Access like the first macro, but the
have the table pull into excel text formatted?

HELP??

--
Campbellj
-----------------------------------------------------------------------
Campbellj4's Profile: http://www.excelforum.com/member.php...fo&userid=2850
View this thread: http://www.excelforum.com/showthread.php?threadid=48100