CommandText Property of importing external data
 

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 " _
, _
"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

CommandText Property of importing external data
 

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

CommandText Property of importing external data
 

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