Hi,
I am currently working on a combination of access plus excel. Both are on 2007 version.
Every month, i would get an excel file that has the same tabs. the excel file would contain the same tabs (5 tabs): summary, new hires, sales, gl accounts, and additional guidelines
the information that i NEED is in the fourth tab (gl accounts) ONLY. 'gl accounts' tab has 7 columns in it. the columns are last name, first name, middle name, sales employee id (e.g: 4301-accounting), country id (e.g: 322-europe), account id (122-lastrox), business unit id (88-primary).
then, i create an additional column for 'full name' using the CONCATENATE formula (combining first name and last name). i would also need to create additional columns and use LEFT formulas to get the first 4 digits of the sales employee id, first 3 digits of country id, first 3 digits of account id, and first 2 digits of business unit id... basically, i want to get ONLY the DIGITS to create the new column of 'gl account id' (using concatenate formula). the result would look something like this:
xxx (sales employee id digits)-xxx (country id digits)-xxx (account id digits)-xx (business unit id digits)..
afterwards i would copy paste values both additional columns. i would also erase all of the columns except the 'full name', 'sales employee id', and 'gl account id'.. so there would only be 3 columns left in the 'gl accounts' tab..
then, i would need to import the edited 'gl accounts' tab to Access for analytical purpose... ONLY the 'gl accounts' TAB in the workbook!
i was just wondering if any of you can help me with the coding. I am VERY NEW with this whole programming stuff and would be grateful for any help you can provide. thank you!
here is the vba code (excel) that i have right now. fyi, i haven't written the code for the import to access. whenever i try this code with the previous month's excel doc. it generates an error. the error is something like this, i created this code using the aug data which has like 10,500 rows of data. then i try this error with the july data which only has 9,000 rows of data. the error is that the code keeps on generating data on the month of july up until row 10,500 even when there's no data... so i get 1,500 "-000---" rows of meaningless data...
![]()
Please Login or Register to view this content.
Bookmarks