I have one Workbook with one worksheet. I want to create a macro that
imports data from 4 different Visual FoxPro data tables. For each
external file I want to create a separate worksheet and rename it
appropriately.
I am getting an error in the macro for creating the second datasource.
The code is shown below:
************************************************************************************************************************
Sub ImportMembers()
'
' ImportMembers Macro
' Macro recorded 9/20/2010 by Fred Blair
'
Dim oSheet As Worksheet, vRet As Variant
Set oSheet = Worksheets.Add
With oSheet
.Name = "Members"
.Cells(1.1).Select
.Activate
End With
'
' Get Members
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=c:
\FlightsGHSA-1\flights
\Data;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Ma" _
), Array("chine;Null=Yes;Deleted=Yes;")),
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT members.memb_id, members.fullname, members.instructor,
members.tower, members.memtype" & Chr(13) & "" & Chr(10) & "FROM
members members" & Chr(13) & "" & Chr(10) & "WHERE (members.memtype<=
$4)" & Chr(13) & "" & Chr(10) & "ORDER BY members.fullname" _
)
.Name = "Query from Visual FoxPro Tables"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
******************************************************************************************************************
This macro works and creates the 'members' worksheet and imports the
data.
If I add to this macro like below I get errors.
Dim oSheet As Worksheet, vRet As Variant
Set oSheet = Worksheets.Add
With oSheet
.Name = "Aircraft"
.Cells(1.1).Select
.Activate
End With
'
' Get Members
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=c:
\FlightsGHSA-1\flights
\Data;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Ma" _
), Array("chine;Null=Yes;Deleted=Yes;")),
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT aircraft.owner_id, aircraft.name, aircraft.pln_desc" &
Chr(13) & "" & Chr(10) & "FROM aircraft aircraft" & Chr(13) & "" &
Chr(10) & Chr(13) & "" & Chr(10) & "ORDER BY aircraft.name" _
)
.Name = "Query from Visual FoxPro Tables"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
When I try to add these additional steps to the macro, it stops at the
".Refresh BackgroundQuery:=False" and highlights it like it can't be
used. I have tried to change the False to True and I have also tried
just deleting the whole line, but it still does not work. It creates
the new worksheet, but does not import the data.
Thanks,
Fred
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
