Shino_skay
asked on
VBA: Import Specific Tab from Excel
Hi,
I'm looking to implement more controls on the VBA importation of Excel. I'm looking to import a specific tab on a spreadsheet but the problem I will have is if someone renames the tab. Does anyone know of a better way?
I thought of the Excel codenames for the worksheets; however, I am unable to figure that part out on my own. Any advice? Thank you.
I'm looking to implement more controls on the VBA importation of Excel. I'm looking to import a specific tab on a spreadsheet but the problem I will have is if someone renames the tab. Does anyone know of a better way?
I thought of the Excel codenames for the worksheets; however, I am unable to figure that part out on my own. Any advice? Thank you.
Sub ImportExcel()
Dim strWorkBookName As String, _
strExcelTabName As String, _
strTableName As String, _
strWorkBookPath As String, _
strWorkBook As String
strWorkBookPath = CurrentProject.Path
'strWorkBookName = "VBA Solution - RV2 ML-BAC Recon.xls"
strWorkBookName = "Book5.xls"
strWorkBook = strWorkBookPath & "\" & strWorkBookName
strExcelTabName = "RV2"
strTableName = "tblRv2Import"
DoCmd.TransferSpreadsheet _
transfertype:=acImport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:=strTableName, _
FileName:=strWorkBook, _
hasfieldnames:=True
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Capricorn1, thanks for replying again. You're saying I can open the excel file in Access, verify if rows 1 has the correct fields before importing? That's exactly what I'm looking for, I can do it in Excel. Actually I already written the VBA codes in Excel but it's such a pain to compare two database downloads that has 30 columns each with anywhere from 80-250 rows then to do calculations on them etc. The maintenance of it is very painful when my boss wants a new type of calculation.
SQL seems the way to go.
Kelvin, thanks for that tip on the exclamation point, I was wondernig why my range wouldn't work. I'm just concern if the end users changes the tab name (for some reason), I'm going ot get emails saying the codes don't work. I guess I'll have to lock the tab name somehow.
SQL seems the way to go.
Kelvin, thanks for that tip on the exclamation point, I was wondernig why my range wouldn't work. I'm just concern if the end users changes the tab name (for some reason), I'm going ot get emails saying the codes don't work. I guess I'll have to lock the tab name somehow.
<You're saying I can open the excel file in Access, verify if rows 1 has the correct fields before importing?>
Yes, do you need help doing this?
if you do, Upload a copy of the Excel file and give details what to look for...
Yes, do you need help doing this?
if you do, Upload a copy of the Excel file and give details what to look for...
ASKER
i need a lot of help. Unfortunately, I can't upload the original data as they are deemed "proprietary". I did include all the column names and a little randomly generated data.
Each week, I download from 2 different databases, one for the week's data (4-9 tab), one fo the week's price (4-9 pricing tab).
The 1st step I would do is a vlookup to grab all the prices from "4-9 Pricing" and fill them in the appropriate fields starting in columns "V" in the "4-9 tab" using the column name "AdminNum" as the lookup value.
Afterwards, I would write the column names (as found in the tab "Calculated Fields") to the end of the data in the "4-9" tab, in this case, after columns "AF". Those "Calculated Fields", I would write simple calculations such as "Days to Maturity" which is:
Public Function YearsToMaturity(DaysToMatu rity As Long) As String
Select Case DaysToMaturity
Case Is > (365 * 20)
YearsToMaturity = "20yr+"
Case Is > (365 * 10)
YearsToMaturity = "10yr-20yr"
Case Is > (365 * 5)
YearsToMaturity = "5yr-10yr"
Case Is > (365 * 2)
YearsToMaturity = "2yr-5yr"
Case Is > 365
YearsToMaturity = "1yr-2yr"
Case Else
YearsToMaturity = "0yr-1yr"
End Select
End Function
I do the same steps for the previoius week's data and I compare them for differences. The difference list is another laundry list of simple calculations, averages, max/mins but I feel once I figure out how to import the data, it should be relatively easy.
The tricky part is, the download for "4-9" tab, the field names would change without much notice, in excel, I would write
shtBondPrice.Rows(1) .Find(strBondPriceColumns_ PriceSht(i ), LookIn:=xlValues, lookat:=xlWhole).Cells.Col umn
If I get an error, I would know someone changed, or maybe even totally excluded the fieild name
Thanks Capricorn, I hope I wasn't too confusing in my explanation.
sample.xls
Each week, I download from 2 different databases, one for the week's data (4-9 tab), one fo the week's price (4-9 pricing tab).
The 1st step I would do is a vlookup to grab all the prices from "4-9 Pricing" and fill them in the appropriate fields starting in columns "V" in the "4-9 tab" using the column name "AdminNum" as the lookup value.
Afterwards, I would write the column names (as found in the tab "Calculated Fields") to the end of the data in the "4-9" tab, in this case, after columns "AF". Those "Calculated Fields", I would write simple calculations such as "Days to Maturity" which is:
Public Function YearsToMaturity(DaysToMatu
Select Case DaysToMaturity
Case Is > (365 * 20)
YearsToMaturity = "20yr+"
Case Is > (365 * 10)
YearsToMaturity = "10yr-20yr"
Case Is > (365 * 5)
YearsToMaturity = "5yr-10yr"
Case Is > (365 * 2)
YearsToMaturity = "2yr-5yr"
Case Is > 365
YearsToMaturity = "1yr-2yr"
Case Else
YearsToMaturity = "0yr-1yr"
End Select
End Function
I do the same steps for the previoius week's data and I compare them for differences. The difference list is another laundry list of simple calculations, averages, max/mins but I feel once I figure out how to import the data, it should be relatively easy.
The tricky part is, the download for "4-9" tab, the field names would change without much notice, in excel, I would write
shtBondPrice.Rows(1) .Find(strBondPriceColumns_
If I get an error, I would know someone changed, or maybe even totally excluded the fieild name
Thanks Capricorn, I hope I wasn't too confusing in my explanation.
sample.xls
ASKER
sorry capricorn, I should have been more clear, from all the columns on the tab "4-9", I only need to make sure a few of them exist:
Maturity Date
AdminNum
USEQ Outstanding Notional
Bond Price w/o Credit upload
Bond Price with Credit upload
CS01 (USD) upload
Duration upload
Maturity Date
AdminNum
USEQ Outstanding Notional
Bond Price w/o Credit upload
Bond Price with Credit upload
CS01 (USD) upload
Duration upload
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ahhhh, i see, you're controlling excel thru Access vbe and checking if the 1st row contains the column names, So after this, I should use the docmd.transfer spreadsheet to a temporary table and then append the data to the table where my sql is looking at.
My final step would be to make sure the append query copies the correct field from the temporary to the actual table. very cool
My final step would be to make sure the append query copies the correct field from the temporary to the actual table. very cool
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I originally thought Capricorn's idea was to utilize a feature in Access but opening Excel from Access is a simple task which I didn't/couldn't think of.
if you do, you can open the excel file (in codes) read and verify, then import