Hi, I'm trying to use the following code, however access is telling me that the user defined variable is not defined.. Now I'm assuming that I am missing a reference for this, however I've got all the ones I can think of selected.. Any ideas?
Code in red is where the error is flagged
References used so far
Thanks for your help
Code in red is where the error is flagged
Code:
Dim strInputFileName As String
Dim objAccess As Object
Dim objExcel As Object
Dim objWorkbook As Object
Dim colWorksheets As Object
Dim objWorksheet As Object
Dim objRange As Object
Dim strWorksheetName As String
[COLOR="Red"]Dim rUsed As Range[/COLOR]
Dim strUsedRange As String
Code:
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(strInputFileName)
Set colWorksheets = objWorkbook.Worksheets
Set rUsed = Intersect(Range("A:D"), ActiveSheet.UsedRange)
strUsedRange = rUsed.Address
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" & strUsedRange
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Stella", strInputFileName, True, strWorksheetName
Next
References used so far
Code:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects Library
Microsoft Office 12.0 Object Library
Microsoft ADO ext. 6.0 for DDL and Security
Thanks for your help