Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/imply fields when importing range into ACCESS
When importing a named range from Excel into Access...
- columns C thru J, rows starting at 6 but ending differently all the time - ... am looking to add 2 named 1-cell ranges from Excel - that are *not* included in the orignal named range - to the import, ie CityID and EventID --- to their corresponding fields in Access. - Excel range to import: MyExcelRange This range begins in column C in Excel. *** Does *not* contain the following in Excel, both of which need to be imported to Access, as many rows as required: - Excel 1-cell range to add/imply upon import to Access - in the 1st field to the left in Access [column B *IF* it were in Excel] : EventID - Excel 1-cell range to add/imply upon import to Access - in the 2nd field to the left in Access [column A *IF* it were in Excel] : CityID Of course, this could be physically re-constructed on another tab in Excel, but ... a) This schema is required for multiple tabs. b) It would be redundantly using large groups of data. b) The workbook size would start to become prohibitive. Any assistance would be greatly appreciated. And please let me know if this needs clarification. Regards, - Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/imply fields when importing range into ACCESS
On Sat, 14 Feb 2009 09:01:01 -0800, MikeF
wrote: When importing a named range from Excel into Access... - columns C thru J, rows starting at 6 but ending differently all the time - .. am looking to add 2 named 1-cell ranges from Excel - that are *not* included in the orignal named range - to the import, ie CityID and EventID --- to their corresponding fields in Access. - Excel range to import: MyExcelRange This range begins in column C in Excel. *** Does *not* contain the following in Excel, both of which need to be imported to Access, as many rows as required: - Excel 1-cell range to add/imply upon import to Access - in the 1st field to the left in Access [column B *IF* it were in Excel] : EventID - Excel 1-cell range to add/imply upon import to Access - in the 2nd field to the left in Access [column A *IF* it were in Excel] : CityID Of course, this could be physically re-constructed on another tab in Excel, but ... a) This schema is required for multiple tabs. b) It would be redundantly using large groups of data. b) The workbook size would start to become prohibitive. Any assistance would be greatly appreciated. And please let me know if this needs clarification. Regards, - Mike How are you importing it into Access? -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/imply fields when importing range into ACCESS
Dick,
Thanx for the reply. Am merely using the Access "Get External Data", then "from Excel". It's a straight import, not a link [although at some point in the future will need to do that]. A few clarifying notes: - The Access table has 6 fields, the first two being EventID and CityID. - The Excel range has 4 fields in columns C thru F [although those fields could be in any 4 contiguous columns]. Those fields exclude EventID and CityID. - EventID and CityID are both named, 1-cell ranges on another worksheet in the same Excel workbook. - It is imperative for the Access table that the values in these 1-cell ranges [EventID and CityID] "hit" the import from Excel, in as many rows as necessary. *** ALSO NOTE - there are dozens of different workbooks containing these same ranges that eventually will need to be imported into the Access table. The values for each of the three ranges are different in each workbook. Does the above help?? "Dick Kusleika" wrote: How are you importing it into Access? -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com On Sat, 14 Feb 2009 09:01:01 -0800, MikeF wrote: When importing a named range from Excel into Access... - columns C thru J, rows starting at 6 but ending differently all the time - .. am looking to add 2 named 1-cell ranges from Excel - that are *not* included in the orignal named range - to the import, ie CityID and EventID --- to their corresponding fields in Access. - Excel range to import: MyExcelRange This range begins in column C in Excel. *** Does *not* contain the following in Excel, both of which need to be imported to Access, as many rows as required: - Excel 1-cell range to add/imply upon import to Access - in the 1st field to the left in Access [column B *IF* it were in Excel] : EventID - Excel 1-cell range to add/imply upon import to Access - in the 2nd field to the left in Access [column A *IF* it were in Excel] : CityID Of course, this could be physically re-constructed on another tab in Excel, but ... a) This schema is required for multiple tabs. b) It would be redundantly using large groups of data. b) The workbook size would start to become prohibitive. Any assistance would be greatly appreciated. And please let me know if this needs clarification. Regards, - Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/imply fields when importing range into ACCESS
On Sat, 14 Feb 2009 12:40:01 -0800, MikeF
wrote: Dick, Thanx for the reply. Am merely using the Access "Get External Data", then "from Excel". It's a straight import, not a link [although at some point in the future will need to do that]. A few clarifying notes: - The Access table has 6 fields, the first two being EventID and CityID. - The Excel range has 4 fields in columns C thru F [although those fields could be in any 4 contiguous columns]. Those fields exclude EventID and CityID. - EventID and CityID are both named, 1-cell ranges on another worksheet in the same Excel workbook. - It is imperative for the Access table that the values in these 1-cell ranges [EventID and CityID] "hit" the import from Excel, in as many rows as necessary. *** ALSO NOTE - there are dozens of different workbooks containing these same ranges that eventually will need to be imported into the Access table. The values for each of the three ranges are different in each workbook. Does the above help?? I assume you mean you want the Access table to have 6 columns, but it doesn't because you're importing from Excel. Three options, as I see it: 1) Insert two columns in front of column C, refer to the CityID and EventID in a formula in those columns, and extend your named range to include those columns. This is the straightforward and obvious answer. You haven't said why you haven't done this, but I assume you can't add the columns for some reason. Would it matter if they were hidden? 2) Make two linked tables in Access; one with your C:F range and one that contains the CityID and EventID. If those two cells aren't side-by-side, you'll need to make an area somewhere that gets them side-by-side and create a range name that covers both of them (Access doesn't like single cell range names for some reason). Now create a query in Access with these two tables and NO JOINS. This will give you a recordset like you want. 3) Don't link the tables via the Access UI. Create the tables in VBA using ADO. This will give you maximum flexibility, but it's the most complex of the options. If any of those options sound good and you need more details or an example, post back. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/imply fields when importing range into ACCESS
"MikeF" wrote: When importing a named range from Excel into Access... - columns C thru J, rows starting at 6 but ending differently all the time - .. am looking to add 2 named 1-cell ranges from Excel - that are *not* included in the orignal named range - to the import, ie CityID and EventID --- to their corresponding fields in Access. - Excel range to import: MyExcelRange This range begins in column C in Excel. *** Does *not* contain the following in Excel, both of which need to be imported to Access, as many rows as required: - Excel 1-cell range to add/imply upon import to Access - in the 1st field to the left in Access [column B *IF* it were in Excel] : EventID - Excel 1-cell range to add/imply upon import to Access - in the 2nd field to the left in Access [column A *IF* it were in Excel] : CityID Of course, this could be physically re-constructed on another tab in Excel, but ... a) This schema is required for multiple tabs. b) It would be redundantly using large groups of data. b) The workbook size would start to become prohibitive. Any assistance would be greatly appreciated. And please let me know if this needs clarification. Regards, - Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/imply fields when importing range into ACCESS
Dick, Re your #1, as follows is in my original msg: Of course, this could be physically re-constructed on another tab in Excel, but ... a) This schema is required for multiple tabs. b) It would be redundantly using large groups of data. b) The workbook size would start to become prohibitive. It may end up though, that I rebuild everything to include the two 1-cell ranges on every worksheet. But as stated above, it would merely be using hundreds of rows of redundant data unnecessarily, on multiple tabs in each workbook. In attempting to keep an already-very-large file-size in check, am hoping for a more elegant solution. Prefer your suggestion #3 as follows. An example would be sincerely appreciated. 3) Don't link the tables via the Access UI. Create the tables in VBA using ADO. This will give you maximum flexibility, but it's the most complex of the options. Regards and thanx. - Mike "Dick Kusleika" wrote: On Sat, 14 Feb 2009 12:40:01 -0800, MikeF wrote: Dick, Thanx for the reply. Am merely using the Access "Get External Data", then "from Excel". It's a straight import, not a link [although at some point in the future will need to do that]. A few clarifying notes: - The Access table has 6 fields, the first two being EventID and CityID. - The Excel range has 4 fields in columns C thru F [although those fields could be in any 4 contiguous columns]. Those fields exclude EventID and CityID. - EventID and CityID are both named, 1-cell ranges on another worksheet in the same Excel workbook. - It is imperative for the Access table that the values in these 1-cell ranges [EventID and CityID] "hit" the import from Excel, in as many rows as necessary. *** ALSO NOTE - there are dozens of different workbooks containing these same ranges that eventually will need to be imported into the Access table. The values for each of the three ranges are different in each workbook. Does the above help?? I assume you mean you want the Access table to have 6 columns, but it doesn't because you're importing from Excel. Three options, as I see it: 1) Insert two columns in front of column C, refer to the CityID and EventID in a formula in those columns, and extend your named range to include those columns. This is the straightforward and obvious answer. You haven't said why you haven't done this, but I assume you can't add the columns for some reason. Would it matter if they were hidden? 2) Make two linked tables in Access; one with your C:F range and one that contains the CityID and EventID. If those two cells aren't side-by-side, you'll need to make an area somewhere that gets them side-by-side and create a range name that covers both of them (Access doesn't like single cell range names for some reason). Now create a query in Access with these two tables and NO JOINS. This will give you a recordset like you want. 3) Don't link the tables via the Access UI. Create the tables in VBA using ADO. This will give you maximum flexibility, but it's the most complex of the options. If any of those options sound good and you need more details or an example, post back. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/imply fields when importing range into ACCESS
On Sun, 15 Feb 2009 05:35:00 -0800, MikeF
wrote: Prefer your suggestion #3 as follows. An example would be sincerely appreciated. 3) Don't link the tables via the Access UI. Create the tables in VBA using ADO. This will give you maximum flexibility, but it's the most complex of the options. Here's the basics Mike. Set a reference to Microsoft ActiveX Data Objects. Sub MakeAccessTable() Dim sqlMake As String Dim sqlDelete As String Dim sqlInsert As String Dim sCon As String Dim rRow As Range Dim rCell As Range Dim rCity As Range Dim rEvent As Range Dim adCon As ADODB.Connection Set rCity = Sheet1.Range("CityID") Set rEvent = Sheet1.Range("EventID") Set adCon = New ADODB.Connection sCon = "DSN=MS Access Database;DBQ=C:\Documents and Settings\Dick\" sCon = sCon & "My Documents\testimport.mdb;DefaultDir=C:\Documents " sCon = sCon & "and Settings\Dick\My Documents;DriverId=25;FIL=MS " sCon = sCon & "Access;MaxBufferSize=2048;PageTimeout=5;" sqlMake = "CREATE TABLE tblMyRange (CityID Long, EventID Long, Field3 " sqlMake = sqlMake & "Long, Field4 Long, Field5 Long, Field6 Long)" sqlDelete = "DROP TABLE tblMyRange" adCon.Open sCon On Error Resume Next adCon.Execute sqlDelete On Error GoTo 0 adCon.Execute sqlMake For Each rRow In Sheet2.Range("MyRange").Rows sqlInsert = "INSERT INTO tblMyRange VALUES (" & _ rCity.Value & ", " & rEvent.Value & ", " For Each rCell In rRow.Cells sqlInsert = sqlInsert & rCell.Value & ", " Next rCell sqlInsert = Left$(sqlInsert, Len(sqlInsert) - 2) & ")" adCon.Execute sqlInsert Next rRow adCon.Close End Sub -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Access DB into Excel changes my calculated fields in Exc | Excel Worksheet Functions | |||
Append key fields to range for ACCESS import | Excel Programming | |||
Append key fields to range for ACCESS import | Excel Programming | |||
Append key fields to range for ACCESS import | Excel Programming | |||
Importing data from Access into Excel: prob w/ cutting off fields | Excel Worksheet Functions |