Importing mutiple tables with the same field names

[Guest]

I have access create tables for each sales consultant and then imported into
a workbook ( i know but thats the way they want to view it).

When i am setting up the users to import into their worksheet using the
wizard I have to click each field for the different user.

Is there a way to import each table by just changing the table name in the
query?

 

[Guest]

Here is what I'd do, if you are permitted. If you have 10 users, create 10
spreadsheets, one for each user. In that sheet, create an odbc-link to that
specific user's spreadsheet. Or if you can only have 1 workbook, I'd then
have 10 sheets in it, one for each user, again with their unique table
linked. Here is how to create that link:

Data>Import External Data>New Database Query. Then select MS Access
DataBase. Then point the file and select the table. When a user opens the
spreadsheet, I'd have a startup macro read the user name, and then hide all
sheets but his, which I'd refresh and display. Here is the code for all that:

Option Explicit


'Then place this on the workbook_Open()

Private Sub Workbook_Open()
Usrid = fOSUserName
Usrid = LCase(Usrid)
For Each wSheet In Worksheets
wSheet.visible=false
Next wSheet
Sheets(usrid).Visible = true 'unhide the one for the person who just opened
this xls file....
cells(1,1).select
Selection.QueryTable.Refresh BackgroundQuery:=False
'the above assumes that the external data is in cell a1 on each sheet.
adjust accordingly...









'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function APIGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = APIGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
If LCase(fOSUserName) = "mhirsch" Then
Let FirstName = "Mike"
ElseIf LCase(fOSUserName) = "msmoot" Then
Let FirstName = "Mark"
ElseIf LCase(fOSUserName) = "lpowell" Then
Let FirstName = "Linda"
ElseIf LCase(fOSUserName) = "cspurgeon" Then
Let FirstName = "Carla"
ElseIf LCase(fOSUserName) = "jkail" Then
Let FirstName = "Judy"
ElseIf LCase(fOSUserName) = "lgoldman" Then
Let FirstName = "Lyn"
ElseIf LCase(fOSUserName) = "jkendall" Then
Let FirstName = "Jennifer"
'ElseIf LCase(fOSUserName) = "" Then
' Let FirstName = ""
'ElseIf LCase(fOSUserName) = "" Then
' Let FirstName = ""
Else
Let FirstName = "User"
End If
End Function
'******************** Code End **************************

 

[Guest]

Thank you for the info but I still have a question

Data>Import External Data>New Database Query. Then select MS Access
DataBase. Then point the file and select the table.

That is what I was doing but I had to choose the field names each time.

My goal is to either have like a SQL query that I can just change the source
and apply it to each worksheet.

 

[Guest]

You need to do one more step:
At the end of the processing of importing external data, select to return
the data to excel. Then just point to where you want it saved. The data is
there. The fields are there. Then if you just select Data>Refresh Data.
That will do exactly what the code-version does. And you don't have to
reselect the fields. Just have your cursor "inside" the area of the external
data, whether in code or manually. No re-selecting fields is needed.

 

[Guest]

I think I fouund what I was looking For.

after setting up the intial query save it as a .dqy you then can open that
and edit it for all the other people easily using find and replace on the
sql view

If you go to
DATA
IMPORT EXTERNAL DATA
chose the .dqy that you saved and edit.
I copy into word and use find and replace wit the table that needs to be there

 

[Guest]

I sure would not do it that way. Way too much work each time. But whatever.

 

[Guest]

I don't think I was clear in what my Initial goal was.
What you provided is perfect after I got each of the queries set up.

I am using Office 2003 and when I was setting up the worksheets initally it
was taking me to the wizard which was where I was trying to save the time and
steps.
I did not want to go through the wizard each time.

Thank you very much for the imput is is very helpfull in moving past even
what I was planning