Nakli


Hi,

I am trying to create an access database that will allow to import excel spreadsheets at the click of the button on a switchboard.

I have been trying for hours to create a module in access that will import excel spreadsheets that contains a table with 12 columns and a different amount of rows for each spreadsheet.

I was able to create an almost complete macro in excel that I copied over to an access module (i was unable to create a Do while that will format the excel file by cutting out the first 8 rows and take out all columns except for 6 of them that i need for importing. It should delete rows below once it finds a row with "Elimination Totals" (Column C) or delete rows belowonce it reaches a blank cell in Column A.

The Functionality of Database that I am trying to create can be described as:

Functionality:

  1. Import Spreadsheet (6 fields/columns) into Access Table with 8 fields(Year & Quarter fields will be entered in by user)
  2. Use macro or module to format the imported file

a) auto expand all columns and rows

b) Delete first 8 rows of spreadsheet

c) (only import columns A, B, C, D, I, & L) delete all of the rest of the columns

d) Read Column B data (truncate or delete the first character in the cell) ex 6050 should be 050; G123 should be 123: keep the remaining three characters in the table.

e) Import the table until Column A reaches a blank value ¡°¡± (maybe a do while function¡­.not sure how these work ) or until Column C = ¡°Elimination Totals¡± Everything on the Elimination Totals line and below should be deleted and not brought into the table in Access.

f) Row 1 & 2 values should be combined to make one row. Example:

Row 1 Column A = Cost; Row 2 Column A = Center; Combine the two and you get one Row = Cost Center. Delete the extra row

  1. The import should be initiated by clicking ¡°Upload Spreadsheet¡± on the Switchboard (I believe I just need to refer to the function name of the import module to accomplish this)

a) When the ¡®Upload Spreadsheet¡¯ button is clicked on the Switchboard, a query should be initiated that requires the user to input the Year value ex: ¡°2007¡± and Quarter value ex: ¡°1¡±. These fields as well as the imported fields should be linked to a table. The table should be pulled by a form preferably a Pivottable sorted by Fiscal Year and Quarter that allows user to change the Agreement Description field(Column C from above) and RA Number (Column L from spreadsheet before formatting) but the other 4 fields should show all and not be able to get modified at all. Data in the Pivottable will be the Agreement Amount value (Column D from above)

b) Another Pivottable will be needed to be sorted by Component (Column I from spreadsheet before formatting). Only the Components should be accessible to change for sorting options here; the rest of the fields should display all and be locked from editing. Data in this Pivottable should also be from the Agreement Amount field/column(Column D from above).

The macro that i used is as follows:

Sub ProviderListMacro()

' ProviderListMacro Macro - Formatting

Range("E9Surprise20").Select
Selection.EntireColumn.Delete
Range("I8Tongue Tied42").Select
Selection.EntireColumn.Delete
ActiveWindow.SmallScroll Down:=21
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-45
Rows("1:8").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "Cost Center"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Agreement Number"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Agreement Description"
With ActiveCell.Characters(Start:=1, Length:=21).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("D1").Select
ActiveCell.FormulaR1C1 = "Agreement Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Organization"
Range("F6").Select
Selection.EntireColumn.Delete
Range("F5").Select
Selection.EntireColumn.Delete
Range("F1").Select
ActiveCell.FormulaR1C1 = "RA Number"
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Range("B10").Select
ActiveWindow.SmallScroll Down:=-9


End Sub

Can someone please assist me on successfully giving the user the ability to import an excel spreadsheet (has the same required information) by creating a module that will import the file and fix the formatting, then save the information to a table and display it on a Form

The form should have two extra fields - Year(2007) and Quarter (ex: 1,2,3,4), which i can/will add to the table.

Any input will be helpful

thanks,

ultsun1





Re: Importing excel spreadsheets over time into Access using a module in Access

Bruno Yu - MSFT


Move the thread from Visual Basic General. There are more exports on supporting Visual Basic for Applications in this forum. I believe you can get the satisfying answers in this forum.

Thanks again for your question.