Using Excel macros in Access

[Lorlai]

Hello All,



I have some excel macros that work great in excel. I am trying to import a sheet into accesss and need to do some formatting on the sheet before the data can be imported. Like I said, these worked when it was just excel, but when I put them into Access, I run into all kinds of errors. I have built a connection to the excel file, then have put in the original macros:

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
'Finds the first empty cell in a column
Range("A1").End(xlDown).Offset(1, 0).Select
'the first few rows of the spreadsheet feature info
'Not necessary for a database. This will detele these rows
Range(ActiveCell.Row & ":" & ActiveCell.Row).Select
Range("1:1", ActiveCell.Row & ":" & ActiveCell.Row).Delete
Range("A1").Select
'Jumps down to the next row Loops through the row until
'there is an empty cell, clearing the color formats and making the
'font black (automatic)
Do
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
With Selection.Font
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    End With
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell)
'Deletes the last row of data
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Delete Shift:=xlUp

The first error I run into highlightsthe xlDown, saying it is an unnamed variable. In excel vba, this is a valid command. Any advice as to what I can do to make this code work in access? Thank you!

[starryNight]

I made a few small changes to your code, namely dimensioning and setting the Excel objects you will be working with. This required me to set the reference to the Excel object library first (Tools->References->Microsoft Excel ##.# Object Library). I like to use early binding when developing, but you could always change this to "Dim # as Object" to use late binding.

The updated code is below. Instead of using "Select" and "Selection" I explicitly create a range object.

Dim oApp As Excel.Application
Dim oWbk As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim oRng As Excel.Range
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'oApp.Workbooks.Open OpenFile.lpstrFile
Set oWbk = oApp.Workbooks.Open("C:\yourFile.xlsx")
Set oSht = oWbk.Worksheets(1) ' Or may use .Activesheet
'Finds the first empty cell in a column
Set oRng = oSht.Range("A1").End(xlDown).Offset(1, 0)

'the first few rows of the spreadsheet feature info
'Not necessary for a database. This will detele these rows
'oSht.Range(ActiveCell.Row & ":" & ActiveCell.Row).Select
'oSht.Range("1:1", ActiveCell.Row & ":" & ActiveCell.Row).Delete
oRng.Delete
'oSht.Range("A1").Select
'Jumps down to the next row Loops through the row until
'there is an empty cell, clearing the color formats and making the
'font black (automatic)
Do
With oRng.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
With oRng.Font
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    End With
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell)
'Deletes the last row of data
oSht.Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
oRng.Delete Shift:=xlUp

[Lorlai]

Hi StarryNight, Thank you so much for your fast and detailed response! I am very very new with vba, and am a little confused. I had missed my whole code when i copied it over, so when I tried to input your suggestions I managed to get myself completly confused. Below is my original full code:

 
Option Explicit[/FONT]
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Sub CreateAccess()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object
OpenFile.lStructSize = Len(OpenFile)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "E:\"
OpenFile.lpstrTitle = "Choose a File"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
'Finds the first empty cell in a column
Range("A1").End(xlDown).Offset(1, 0).Select
'the first few rows of the spreadsheet feature info
'Not necessary for a database. This will detele these rows
Range(ActiveCell.Row & ":" & ActiveCell.Row).Select
Range("1:1", ActiveCell.Row & ":" & ActiveCell.Row).Delete
Range("A1").Select
'Jumps down to the next row Loops through the row until
'there is an empty cell, clearing the color formats and making the
'font black (automatic)
Do
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell)
'Deletes the last row of data
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Delete Shift:=xlUp
With oApp
.Visible = True
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, "Test_DB", OpenFile.lpstrFile, True
 
End With
Set oApp = Nothing
End Sub

I'm trying to make this an automated process, and thought I would start with the user choosing the file to import. I am having trouble switching the hard coded file location to the more variable user inputed file diaglog approach. Any more of your fabulous advice?