I'm using Access and Excel 2007. I would VERY much appreciate it if someone
could help me. If an Excel guru could help me out with item (1), I would be
very grateful. If you know how to help me with (2) also, I would be grateful
too.

I have a simple Access database with only one table and one form. My code
runs some VBA to open and Excel spreadsheet to get filenames and create
hyperlinks to pull back into an Access table.

I have one Button on the Access form (cmdRefresh) in the Form Header section
and three fields in the Form Detail section. The table field names for
tblHyperlink
a ID, Filename, and LinkURL.

All this database is doing is looking in a static directory and filling
column A in an Excel spreadsheet with jpg filenames. Then it creates
hyperlinks to the jpg files in column B. Then it imports the Excel
spreadsheet back into the Access table named tblHyperlinks.

(1) The problem is that it runs okay the first time I click the button and
the second time I click the button it gives me a Run-time error 1004 Method
Worksheet of Object _Global failed. (So I actually need help with this).
Plus...

(2) I didnt notice until I had 182,000 records in the tblHyperlink table
that it needs to "clean out" the tblHyperlinks table before it Imports, so I
put a line of code to delete the tblHyperlinks before it imports the Excel
spreadsheet, and it gives me a Run-time error 3211 the database engine could
not lock table 'tblHyperlinks' because it is already in use by another person
or process.

The code behind the cmdRefresh button is this:

Private Sub cmdRefresh_Click()
Call ListFilenames
End Sub

The ListFilenames code in the basRefreshHyperlinks module is this:

Public Sub ListFilenames()
'================================================= ===
'Initialize variables
'================================================= ===
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Directory As String
Dim Filename As String
Dim MySheet As Worksheet
Dim rw As Long
Dim LastRow As Long
Dim picCnt As Integer

' On Error GoTo TCOT
' DoCmd.SetWarnings False

'Show Excel. This is optional.
xl.Visible = True

'Open the workbook.
Set wb = xl.workbooks.Open("C:\temp\Hyperlinks.xls")

'Get a reference to the first worksheet.
Set ws = wb.Worksheets("Sheet1")

picCnt = 0

'================================================= ===
'Activate Filenames worksheet
'================================================= ===
Worksheets("Sheet1").Activate
Set MySheet = ActiveSheet
Set ws = ActiveSheet

'================================================= ===
'Delete columns A and B
'================================================= ===
MySheet.Columns("A:B").Delete Shift:=xlToLeft
'Columns("A:B").Delete Shift:=xlToLeft

'================================================= ===
'Add Column Headers
'================================================= ===
Range("A1").Select
ActiveCell.FormulaR1C1 = "Filename"
Range("B1").Select
ActiveCell.FormulaR1C1 = "LinkURL"

'================================================= ===
'Change the directory below as needed
'================================================= ===
Directory = "N:\Parts\"
If Left(Directory, 1) < "\" Then
Directory = Directory & "\"
End If

Filename = Dir(Directory & "*.jpg")

'================================================= ===
'Populate column A with filenames
'================================================= ===
rw = 2
Do While Filename < ""
MySheet.Cells(rw, 1).Value = Filename
'ws.Cells(rw, 1).Value = Filename
rw = rw + 1
Filename = Dir
'picCnt = picCnt + 1
Loop

'================================================= ===
'Find the row number of the last record
'================================================= ===
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
'LastRow = ws.Range("A65536").End(xlUp).Row

'================================================= ===
'Create Hyperlinks
'================================================= ===
With Worksheets("Sheet1").Range("B2")
'With ws.Range("B2")
.FormulaR1C1 = "=HYPERLINK(""N:\Parts\""&RC[-1])"
.AutoFill Destination:=Range("B2:B" & LastRow)
End With

'================================================= ===
'Format worksheet
'================================================= ===
ws.Columns("A:B").EntireColumn.AutoFit
'MsgBox "Number of pics: " & picCnt, vbOKOnly

'================================================= ===
'Delete tblHyperlinks table and Import Hyperlinks Spreadsheet
'================================================= ===
'DoCmd.DeleteObject acTable, "tblHyperlinks"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"tblHyperlinks", "X:\Temp\Hyperlinks.xls", True, "Sheet1!"

'================================================= ===
'Wrap up
'================================================= ===
ws.Range("A1").Select

'Save
wb.Save

'Close the workbook.
wb.Close

'Quit Excel
xl.Quit

Set ws = Nothing
Set wb = Nothing
Set xl = Nothing
Set MySheet = Nothing

' DoCmd.SetWarnings True
'
'TCOT:
' Exit Sub

End Sub

--
Other programming languages I''''ve used a Cobol, C++, Informix Database,
and Unix.