jolivanes
01-16-2010, 12:23 AM
I am taking information from an .MDB file. To get the required information I have to get through 3 tables and find numbers.
From the .MDB file, I first have to open an xlCmdTable called "JobInfo" and load all entries of column B (B2 and on) into a Userform ComboBox.
I make a selection from these entries in the ComboBox and then a number to the left of this selection from the entries in Column B has to be stored in memory. That number is in the same row as the selection but in Column A of the table. The table needs to be closed without saving.
I then open another table called "Path" and find the previously stored number in column D of this table. I now have to store the number in the same row but in Column A from this table. Table needs to be closed without saving. Now I have to open the third table, called "Params", copy columns A to P into my workbook, close the table without saving and delete all rows that do not have the last stored number from the second opened table in Column B. In all tables, the first row has headers. The last table to be opened, "Params" has currently more then 8000 rows filled and it is growing.
At the moment I get all of this done by copying the relevant columns into my workbook and find the required numbers with Index Match formulas. Once the last table is copied into my workbook I delete the columns used to search and the formulas. To say the least, a cluncky way of doing it. Could all this be done in one go without copying the columns into my workbook?
The code to open the tables (with help from xld):
Sub GetMyPrivateMDB()
Application.ScreenUpdating = False
Workbooks.OpenDatabase Filename:="C:\C\MyPrivate.MDB", CommandText:=Array( _
"JobInfo"), CommandType:=xlCmdTable
Set wb = ActiveWorkbook
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 1)).Copy
Windows("My MDB Workbook.xls").Activate
Range("AA1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
wb.Close SaveChanges:=False
Workbooks.OpenDatabase Filename:="C:\C\MyPrivate.MDB", CommandText:=Array( _
"Path"), CommandType:=xlCmdTable
Set wb = ActiveWorkbook
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Copy
Windows("My MDB Workbook.xls").Activate
Range("AC1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
wb.Close SaveChanges:=False
Range("G1").Select
UserForm6.Show vbModeless 'This has the combobox
End Sub
Any help is greatly appreciated
Regards
John
From the .MDB file, I first have to open an xlCmdTable called "JobInfo" and load all entries of column B (B2 and on) into a Userform ComboBox.
I make a selection from these entries in the ComboBox and then a number to the left of this selection from the entries in Column B has to be stored in memory. That number is in the same row as the selection but in Column A of the table. The table needs to be closed without saving.
I then open another table called "Path" and find the previously stored number in column D of this table. I now have to store the number in the same row but in Column A from this table. Table needs to be closed without saving. Now I have to open the third table, called "Params", copy columns A to P into my workbook, close the table without saving and delete all rows that do not have the last stored number from the second opened table in Column B. In all tables, the first row has headers. The last table to be opened, "Params" has currently more then 8000 rows filled and it is growing.
At the moment I get all of this done by copying the relevant columns into my workbook and find the required numbers with Index Match formulas. Once the last table is copied into my workbook I delete the columns used to search and the formulas. To say the least, a cluncky way of doing it. Could all this be done in one go without copying the columns into my workbook?
The code to open the tables (with help from xld):
Sub GetMyPrivateMDB()
Application.ScreenUpdating = False
Workbooks.OpenDatabase Filename:="C:\C\MyPrivate.MDB", CommandText:=Array( _
"JobInfo"), CommandType:=xlCmdTable
Set wb = ActiveWorkbook
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 1)).Copy
Windows("My MDB Workbook.xls").Activate
Range("AA1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
wb.Close SaveChanges:=False
Workbooks.OpenDatabase Filename:="C:\C\MyPrivate.MDB", CommandText:=Array( _
"Path"), CommandType:=xlCmdTable
Set wb = ActiveWorkbook
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Copy
Windows("My MDB Workbook.xls").Activate
Range("AC1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
wb.Close SaveChanges:=False
Range("G1").Select
UserForm6.Show vbModeless 'This has the combobox
End Sub
Any help is greatly appreciated
Regards
John