How did you strip the path out of the variable data? It sounds like you may
have solved it.

" wrote:

Hey ... solved my own issue ... when I inserted the variable field I
didn't wholly take out the file path ... any who... for all of you out
there the correct code should be:

Private Sub ListBox1_Click()


TextBox2.Text = ListBox1.Text


End Sub


Private Sub UserForm_Activate()


With UserForm1.ComboBox1
..AddItem ("Data1")
..AddItem ("Data2")
..AddItem ("Data3")
End With


End Sub


Private Sub combobox1_click()


Dim file As String
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Worksheet


file = Application.GetOpenFilename()


TextBox1.Text = file


Set xl = New Excel.Application
xl.Visible = False
Set wkb = xl.Workbooks.Open(file)


'Populate the list box with the selected workbooks worksheet names
With UserForm1.ListBox1
..Clear
For Each wks In xl.Worksheets
..AddItem (wks.name)
Next
End With


wkb.Close


Set xl = Nothing
Set wkb = Nothing


End Sub


Private Sub CommandButton1_click()


Dim file As String
Dim name As String
Dim sheetname As Variant


file = TextBox1.Text


sheetname = TextBox2.Text


name = "'" & sheetname & "$'"


If ComboBox1.Text = "DDS Data" Then
Sheets("DDS").Activate
ElseIf ComboBox1.Text = "NSI Data" Then
Sheets("NSI").Activate
Else
Sheets("SQAD").Activate
End If


'Import the selected worksheet from the selected workbook
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=" & file & "" _
, _
";Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet " _
, _
"OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OL" _
, _
"EDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Datab" _
, _
"ase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = array(name)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = file
.Refresh BackgroundQuery:=False
End With


End Sub


This is a good code to import external data but still have some input
on what you are importing.

Ed