U
Ultsun1
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:
Import Spreadsheet (6 fields/columns) into Access Table with 8
fields(Year & Quarter fields will be entered in by user)
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
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("E920").Select
Selection.EntireColumn.Delete
Range("I842").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
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:
Import Spreadsheet (6 fields/columns) into Access Table with 8
fields(Year & Quarter fields will be entered in by user)
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
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("E920").Select
Selection.EntireColumn.Delete
Range("I842").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