troubleshooting Question

Copy data from Excel into MSAccess

Avatar of jay_waugh
jay_waughFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVBA
16 Comments3 Solutions126 ViewsLast Modified:
I am trying to open a spreadsheet from MSAccess using VBA. I then want to select some cells on the spreadsheet to copy the data into an access table. At the moment I can't even get the code to select the cells on the spreadsheet to work. Can you help please?

Private Sub opwb()

Dim sPath As String
Dim sFile As String
Dim WorkbookToRead As String
Dim excelApp As Excel.Application
Dim wb As Excel.Workbook
Dim Flag As String
Dim strPath As String
Dim strAppFPSS As String
Dim strAppId As String
Dim fpHostnames As Variant
Dim strWorkSheetName As String
Dim hn As Range

strPath = "C:\temp\"
strAppFPSS = "Doc1"
strAppId = Mid(strAppFPSS, 1, InStr(1, strAppFPSS, " ") - 1)
strWorkBook = strPath & strAppFPSS & ".xlsx"
strWorkSheetName = "payments"
On Error Resume Next

Set excelApp = GetObject(, "Excel.Application")  ' - If Excel is not open, an error occurs and Err.Number > 0
                Flag = 1
If Err.Number <> 0 Then Set excelApp = CreateObject("Excel.Application") 'There is an error if Err.Number <> 0     Create Excel.Application

On Error GoTo Err_Handler
                Flag = 2

    With excelApp
        .Visible = True  'Excel becomes visible. Confirmed.
        Set wb = .Workbooks.Open(strWorkBook, ReadOnly:=True)
        Sheets(strWorkSheetName).Range(Selection, Selection.End(xlDown)).Select
    End With
    Set wb = Nothing
    Set excelApp = Nothing

    Exit Sub

    Set wb = Nothing
    Set excelApp = Nothing
    MsgBox Err.Description
    MsgBox "An error occurred while in Private Sub OpenExcelWorkbook(), called from cmdOpenExcelWorkbook_Click() | Flag = " & Flag
    Resume Exit_Sub

End Sub
Join our community to see this answer!
Unlock 3 Answers and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros