VB code to transfer from an open excel book?

[Kylep]

I am trying to write some code to import some excel books into a table. The book needs to be heavily edited before it can be imported, and I have accomplished all of that, but I'm having trouble with the line that actually imports the worksheet into my table. Here is what I have:

Sub gatherdata()
'set variables
    Dim xlApp As New Excel.Application
    Dim wb As workbook
    Dim ws As worksheet
    Dim FilePath As String
    
FilePath = "C:\test.xls"
    
    xlApp.Visible = True
    
    Set wb = xlApp.workbooks.Open(FilePath)
    Set ws = wb.ActiveSheet
    
'Begin altering data
 '(code here)
'Data is altered to look exactly like it should in the table

        
        DoCmd.TransferSpreadsheet acImport, 8, _
"tblTemp", wb, True

'Close book
    Set ws = Nothing
    wb.Close
    Set wb = Nothing
    xlApp.Quit
    
End Sub

My problem is with (i think):

        DoCmd.TransferSpreadsheet acImport, 8, _
"tblTemp", wb, True

The error I get is: "An Expression you entered is the wrong data type for one of the arguements." I'm sure this refers to the wb variable needing to be a path and file name, but I don't want the code to open another book, what it needs is already open.

Can anyone help?!

 

[boblarson]

Well one thing I noticed is that you have

DoCmd.TransferSpreadsheet acImport, 8, _
"tblTemp", wb, True

and it should be

DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel8, _
"tblTemp", wb, True

 

[Kylep]

Thanks for the feedback Bob. I've actually found that 8 is sufficiant for that setting, I've used that in simular code to import a whole saved workbook and it worked. None the less I tried changing it to your suggestion to be sure and I get the same message.

I'm positive my problem is with the use of that variable, but I don't know how else to tell it to use the open workbook!

 

[boblarson]

Have you tried setting a string variable to xlApp.Path and using that?

 

[Kylep]

I'm still a bit of a noob, and although i've been searching i can't seem to find the proper use of xlsApp.path. Would you mind spelling it out for me?

 

[boblarson]

Sorry, my bad -

You need to use the wb.Path and wb.Name (for example)

strPath = wb.Path & "\" & wb.Name

 

[Kylep]

Ok, so that would set the variable to the path and name of the book currently open, but that would only work if I actually saved the book with all the adjustments the macro just made. That is not out of the question but it seems like a waste to save and close the book on one line just to use this code to open it up and import the data on the next. Know what i mean?

 

[boblarson]

Well according to your code, you are already opening a workbook that exists. If necessary, you would probably have to save it, do the import, and then kill the file.

 

[Kylep]

Ok, thanks for talking this through with me. I'll just save it and do the import from the saved file and move on with life.

If anyone happens to know how to grab the already open worksheet though, feel free to speak up.

 

[accessjpm]

did you need to grab the whole spreadsheet or just a range of cells? I'm working on something similar right now

 

[vikrantbaliyan]

hi friends,

can anybody help me

well i am using DoCmd to transfer excel to table like,

Private Sub test(strFile As String)

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim foundFirst, foundFirstScen, foundIns As Excel.Range
    Dim Sheet As Excel.Worksheet, arrIns, lnghwd As Long

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open(strFile, False)
    Set arrIns = Nothing
    lnghwd = xlApp.hWnd
    xlApp.EnableEvents = True
    xlApp.DisplayAlerts = False
    xlApp.Visible = False

    For Each Sheet In xlBook.Worksheets
    
        With Sheet

            rowLim = .Cells.SpecialCells(xlCellTypeLastCell).Row
            colLim = .Cells.SpecialCells(xlCellTypeLastCell).Column
    
            Set foundIns = .Range(.Cells(1, 1), .Cells(rowLim, colLim)).Find("~~VK", LookIn:=xlValues, lookat:=xlWhole)
            If Not foundIns Is Nothing Then
                
                Call ImportDataInToTable_By_DoCmd(Sheet.Name, foundIns, strFile, "TEST")
                
            End If

        End With
        
    Next
        
    xlBook.Close True
    xlApp.Quit
    Set xlBook = Nothing
    Set xlApp = Nothing
    
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open(strFile, False)
    xlBook.Close True
    xlApp.Quit
    Set xlBook = Nothing
    Set xlApp = Nothing
    
    Call TerminateExcel(lnghwd)
    
End Sub

Public Function ImportDataInToTable_By_DoCmd(strSheetName As String, foundIns As Excel.Range, strXLFile As String, strTableName As String)
        '<EhHeader>
        On Error GoTo ImportDataInToTable_By_DoCmd_Err
        '</EhHeader>
        
        Dim strRange As String
        Dim acApp As New Access.Application
        Dim intCnt As Integer
        acApp.Visible = False
        'Update the Yr Field names
100     For intCnt = 1 To foundIns.CurrentRegion.Columns.Count
102         strRange = foundIns.CurrentRegion(2, intCnt)
104         If IsNumeric(strRange) Then
106             foundIns.CurrentRegion(2, intCnt) = "\~" & foundIns.CurrentRegion(2, intCnt)
            End If
        Next
        
        'Import the data in a Specified table on given criteria
110     strRange = foundIns.CurrentRegion.Address
        If InStr(1, strRange, ":", vbTextCompare) <> 0 Then

112         strRange = Mid(strRange, InStrRev(strRange, "$", InStr(1, strRange, ":", vbTextCompare), vbTextCompare), InStr(1, strRange, ":", vbTextCompare) - InStrRev(strRange, "$", InStr(1, strRange, ":", vbTextCompare), vbTextCompare) + 1)
114         strRange = Replace(foundIns.CurrentRegion.Address, strRange, "$" & foundIns.Row + 1 & ":")
116         strRange = Replace(strRange, "$", "", , , vbTextCompare)
'118         acApp.Application.AutomationSecurity = msoAutomationSecurityLow
           
            'open the TempDb
120         acApp.OpenCurrentDatabase gstrCTempDB, False

            'Transfer the data into Temp Table for Processing
122         acApp.DoCmd.TransferSpreadsheet acImport, 8, strTableName, foundIns.Application.ActiveWorkbook.Path & "\" & foundIns.Application.ActiveWorkbook.Name, True, strSheetName & "!" & strRange
124         acApp.CloseCurrentDatabase
            acApp.Quit
126         Set acApp = Nothing

        End If
        
        Call TerminateExcel
        
        '<EhFooter>
        Exit Function

ImportDataInToTable_By_DoCmd_Err:
'        ErrReport Err.Description, "VEDA_FE.modcommonfunctions.ImportDataInToTable_By_DoCmd", Erl
        Resume Next
        '</EhFooter>
End Function

this code is working perfectly but i am facing problem with this when import is on progress for a file and i am working on another excel file, at that time importing is open in open instance of Excel.otherwise everything is fine.

my regd. is working on another excel file when an other excel file is importing because my import data is too so takes long time to import in mean time i want to work on excel files other than import file.

thanks in advance

 

[Rabbie]

I have found that Excel does not like to do two things at the same time. So I think you are going to have to live with not being able to work on another Excel file while you are importing from the other file.

 

[vikrantbaliyan]

I have found that Excel does not like to do two things at the same time. So I think you are going to have to live with not being able to work on another Excel file while you are importing from the other file.

well dear there is no way to work together?

but MS Access provide us same funtionalaty then why not in Excel.

 

[Rabbie]

well dear there is no way to work together?

but MS Access provide us same funtionalaty then why not in Excel.

Only one person can work on an Excel spreadsheet at a given time but several people can use the same Access DB. Just the way MS have written these products I guess.

 

[vikrantbaliyan]

dear rabbie

if i am not using docmd and import file data by range then we can work on multiple.