How to adjust spreadsheets through Access?

[Guest]

Hi.

I have a spreadsheet that is pulled from another program I have no control
over. This spreadsheet needs to be imported into a database I maintain in
Access.

I have a table set up for this purpose. My problem arises in that the Excel
sheet has excess blank rows and several others portions that need to be
removed. I have some code to deal with this:

Sub Adjust_Objects()

Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").ColumnWidth = 12.29
Columns("J:J").ColumnWidth = 24
Columns("K:K").Select
Range("K192").Activate
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Range("G192").Activate
Selection.Delete Shift:=xlToLeft
Rows("190:191").Select
Selection.Delete Shift:=xlUp
Range("A2").Select

End Sub

That said, is there a way I can run this code on the sheet through Access
then have the correctly adjusted sheet imported into my table?

Still somewhat new to VB.

Any help would be greatly appreciated.

Thanks

 

[John Nurick]

Hi Chris,

The following links should be helpful:
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Run Excel macros through Automation
http://www.mvps.org/access/modules/mdl0007.htm

Importing Excel spreadsheets from code
http://www.mvps.org/access/general/gen0008.htm

Using Automation to Create and Manipulate an Excel Workbook (Q210148)
http://support.microsoft.com/?id=210148

It's usually best avoid using the Selection object when automating Excel
(or Word). Instead of first selecting a range and then working on it,
work on the range directly. E.g. instead of

Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft

you could use something like

With objXL.ActiveWorkbook.Sheets("Sheet1")
.Rows("1:3").Delete
.Columns(1).Delete
...
End With

 

[Guest]

Thanks John, the information you provided was quite informative and helpful.

However, I am still running into a couple of issues.

I have the following code bound to a button on my form:

Private Sub Import_Objects_Click()

DoCmd.RunSQL "Delete * FROM ApproTest"
DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="ApproTest", FileName:="C:\Budget Report-Adult-Gen03.xls", _
Hasfieldnames:=True, Range:="Budget Report-Adult Gen 03!B4:F192"
MsgBox "Objects Data Successfully Imported"

End Sub

My question is instead of having the file pulled from C:\, is there anyway I
can let the user browse for the spreadsheet then have the code run on it?
Additionally, instead of a selected range (which will not work since activity
changes each month), I would like to remove the first column in the sheet
(which is blank), and also exclude the last two rows(which are totals, not
needed in my table) no matter what size the spreadsheet is.

Can this be done programmatically after the user selects the spreadsheet to
import?

I'm sure this is trivial to most of you guys, so please excuse my novice VB
hehe.

Once again, thanks for any assistance or recommendations.

 

[John Nurick]

Hi Chris,

Use the code at http://www.mvps.org/access/api/api0001.htm to display
the standard File Open dialog and get the filespec into a string
variable. You can then use this first when you use Automation to create
an instance of Excel to open the workbook and manipulate the worksheet,
and then, after saving the workbook, pass it to TransferSpreadsheet in
place of the literal filespec.

If you explore the Excel object model you will find ways of doing things
like locating the bottom of the data in a worksheet so you can delete
the last two lines. If the worksheet is "clean" - nothing on it except
the table of data - something like this might do it:

With objXL.ActiveWorkbook.Sheets("Sheet1")
...
'Delete last two rows
For j = 1 to 2
.UsedRange.Rows(.UsedRange.Rows.Count).Delete
Next
End With