CommandText Property of importing external data

[eschneids01]

I am attempting to automate the process of importing external data
within my project. I have a dialog box appear that has the user select
the file they are pulling from. a list box is then populated with the
worksheet names from that file. they select a worksheet name and they
are supposed to be able to click a button and have the data be
imported. However I recieve a runtime error 1004 saying that the
microsoft jet database cannot find the object that i am looking for (I
know its there ... yes its spelled correctly)

Am I doing something wrong when I pass the variable to the CommandText
property. Any help is appreciated.

Here is my code for review:

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 & "" _
, _
"CPE writer.xls;Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet " _
, _
"OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDBatabase 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 OLEDBon'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

 

[eschneids01]

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 " _
, _
"OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDBatabase 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 OLEDBon'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

 

[MentalDrow]

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