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...

Sub GL_String_Import_Access()
'
' GL_String_Import_Access Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Full Name"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-2])"
    Selection.AutoFill Destination:=Range("C2:C9219")
    Range("C2:C9219").Select
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Account ID"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K9219")
    Range("K2:K9219").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    Columns("W:W").EntireColumn.AutoFit
    Columns("X:X").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "Country ID"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)"
    Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X9219")
    Range("X2:X9219").Select
    Columns("Z:Z").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "Business Unit ID"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],2)"
    Range("Z2").Select
    Selection.AutoFill Destination:=Range("Z2:Z9219")
    Range("Z2:Z9219").Select
    ActiveWindow.ScrollColumn = 19
    Range("AB1").Select
    Columns("AA:AA").EntireColumn.AutoFit
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveCell.FormulaR1C1 = "GL Account ID"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-8],""-"",""000"",""-"",RC[-4],""-"",RC[-17],""-"",RC[-2])"
    Columns("AB:AB").EntireColumn.AutoFit
    Range("AA1").Select
    Selection.Copy
    Range("AB1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("AB2").Select
    Selection.AutoFill Destination:=Range("AB2:AB9219"), Type:=xlFillDefault
    Range("AB2:AB9219").Select
    Range("AC9223").Select
    ActiveWindow.ScrollRow = 9195
    ActiveWindow.ScrollRow = 9176
    ActiveWindow.ScrollRow = 9156
    ActiveWindow.ScrollRow = 9117
    ActiveWindow.ScrollRow = 9078
    ActiveWindow.ScrollRow = 8981
    ActiveWindow.ScrollRow = 8864
    ActiveWindow.ScrollRow = 8669
    ActiveWindow.ScrollRow = 8435
    ActiveWindow.ScrollRow = 8143
    ActiveWindow.ScrollRow = 7773
    ActiveWindow.ScrollRow = 7403
    ActiveWindow.ScrollRow = 7072
    ActiveWindow.ScrollRow = 6682
    ActiveWindow.ScrollRow = 6195
    ActiveWindow.ScrollRow = 5747
    ActiveWindow.ScrollRow = 5338
    ActiveWindow.ScrollRow = 4949
    ActiveWindow.ScrollRow = 4695
    ActiveWindow.ScrollRow = 4423
    ActiveWindow.ScrollRow = 4208
    ActiveWindow.ScrollRow = 4053
    ActiveWindow.ScrollRow = 3936
    ActiveWindow.ScrollRow = 3877
    ActiveWindow.ScrollRow = 3838
    ActiveWindow.ScrollRow = 3819
    ActiveWindow.ScrollRow = 3799
    ActiveWindow.ScrollRow = 3780
    ActiveWindow.ScrollRow = 3741
    ActiveWindow.ScrollRow = 3624
    ActiveWindow.ScrollRow = 3410
    ActiveWindow.ScrollRow = 2942
    ActiveWindow.ScrollRow = 2611
    ActiveWindow.ScrollRow = 2319
    ActiveWindow.ScrollRow = 2046
    ActiveWindow.ScrollRow = 1774
    ActiveWindow.ScrollRow = 1520
    ActiveWindow.ScrollRow = 1306
    ActiveWindow.ScrollRow = 1092
    ActiveWindow.ScrollRow = 936
    ActiveWindow.ScrollRow = 741
    ActiveWindow.ScrollRow = 585
    ActiveWindow.ScrollRow = 410
    ActiveWindow.ScrollRow = 293
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 1
    Columns("AB:AB").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "GL Account ID"
    Range("AB2").Select
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Full Name"
    Range("A:B,D:D,F:AA").Select
    Range("F1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:C").Select
    Columns("A:C").EntireColumn.AutoFit
    Range("A1").Select
End Sub