LinkBack URL
About LinkBacksI 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
Register To Reply
Bookmarks