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:
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?