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 Comments1 Solution126 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")  'http://support.microsoft.com/kb/288902 - 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)
        wb.Sheets(8).Sheets(strWorkSheetName).Range("A2").Select
        Sheets(strWorkSheetName).Range(Selection, Selection.End(xlDown)).Select
               
    End With
             
               
    Set wb = Nothing
    Set excelApp = Nothing

Exit_Sub:
    Exit Sub

Err_Handler:
    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 the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 16 Comments.
Join the Community
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 1 Answer 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