Importing using docmd transferspreadsheet Results

Hey,

So I'm just brainstorming a little here and I need the foundation to be right which is where I've gone wrong in the past. I need a database to be able to read four dynamic spreadsheets but only one particular sheet of it, then filter or clean that data and ammend or add to an existing table in the db. So what I'm thinking is docmd.transferspreadsheet then dump that into a temporary table, compare serial numbers (it's an asset database) and if duplicate serial numbers then obviously it's an ammendment if no duplicate then it's a new entry and update the serial with the new details. clear the temp table so my forms and search functions all read from the one table. Do you think that's the best way?

This is driving me mad, please help.

I have written the follow code;

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strImportTable, strImportFilePath, True, "200508!A:F"

200508 is the sheet name.

When I run the code I geta message saying 'can't find object '200508$A:F'.

Why would the '!' be replaced with '$' when the code is run.

I am using Access 97

Thanks

Stu

I have a facility whereby I import data from Excel spreadsheets into Access using some VBA code.

The users have the ability to add new worksheets to their files (seldom used in practice) and I add these new names to my master list, together with some other attributes, and all sheets are thereafter automatically imported using DoCmd.Transferspreadsheet.

The problem for me is that someone has added a worksheet name with an ampersand in it. VBA then fails to import it (I've suppressed the messages so can't remember what (if anything) was reported when it failed) and chugs onto the next sheet.

Is there any way around this - should I hold the sheet name with a double ampersand in my master list? Otherwise, should I just keep asking users not to give Excel sheets name with an ampersand in them?

Hello

I am currently trying to get a spreadsheet to import into an access table at the click of a button. The code I am currently using is below

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Details", "filepathLearnerSupportCollegeProvision.xls", True, " "

I have tried this and a few variations with no success. The most common error is "Unexpected Error from External Database (22)

Can anyone suggest a solution to this?

Many thanks

My colleagues can't be trusted to import the data into a table using file->get external data -> import!

Everything works using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tablename", "Excelfilename", True

But can I get a dialog box up for "Excelfilename" so that they can browse to a file? Or is there another way?

Many thanks.

Hello,

I'm trying to use VBA to implement a routine to automate queries and updates which must happen in sequence. Given that I am but a beginner in VBA coding, I'm struggling to make this work.

Below you'll find the code where I use DoCmd.RunSQL several times. I'd rather declare the SQL statements as Strings and then run them, but I don't know how to do it. Further this code below only works up to a point and then throws and error about misuse of the SELECT statement...

Thanks for your help!

Barbacuca

#### CODE ####

' *** IMPORT NEW CMTO ***
'
' This routine perfomrs 5 actions:
' 1) Deletes current content in CMTO table (qry_deleteCMTOrecords)
' 2) Imports table CMTO_ST.xls (DoCmd.TransferSpreadsheet)
' 3) Deletes content in lutbl_Identifier (qry_deleteIdentifier)
' 4) Finds all unique new identifiers in identifier-field in CMTO (qry_findAllPossibleIdentifier)
' 5) Appends new identifiers to lutbl_Identifier (qry_appendNewIdentifier)


Private Sub ImportNewCMTO_Click()
On Error GoTo ImportNewCMTO_Click_Err

' Deletes old CMTO contents
DoCmd.RunSQL "DELETE CMTO.*" _
& "FROM CMTO;", -1

' Imports CMTO
DoCmd.TransferSpreadsheet acImport, 8, "CMTO", "" _
& "W:ProjectControls" _
& "EstimateSystemSourcesCMTO_ST.xls", True, ""

' Deletes old identifier contents
DoCmd.RunSQL "DELETE lutbl_Identifier.*" _
& "FROM lutbl_Identifier;"

' Finds new identifiers
DoCmd.RunSQL "SELECT First(CMTO.identifier) AS [identifier Field], Count(CMTO.identifier) AS NumberOfDups" _
& "FROM CMTO" _
& "GROUP BY CMTO.identifier" _
& "HAVING (((Count(CMTO.identifier))>1));"

' Appends new identifiers to Identifier table
DoCmd.RunSQL "INSERT INTO lutbl_Identifier" _
& "SELECT qry_findAllPossibleIdentifiers.*" _
& "FROM qry_findAllPossibleIdentifiers;"

ImportNewCMTO_Click_Exit:
Exit Sub
ImportNewCMTO_Click_Err:
MsgBox Error$
Resume ImportNewCMTO_Click_Exit
End Sub

Alright I need to set the focus to a textbox right after importing some data from an excel spreadsheet. It seems that after finishing the import, focus in no where on my form, furthermore even though the rest of the code runs it doesn't set the focus to the textbox. Is there a way to get the focus back to the textbox in the same sub? The end user will be using a scanner so the less interaction the better. Here is the code:

Dim WbPath As String
WbPath = filepath & filename & "_" & filerevision & ".xls"
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(WbPath)

'Get sheet count to loop through all the Excel sheets
SheetCount = xlWb.Worksheets.Count

'Loop through sheets
For lngCounter = 2 To SheetCount
Set xlWs = xlWb.Sheets(lngCounter)
'Set xlRng = xlWs.Range("A1")

'Get Address
GrabAddress = xlWs.Name & "!"
'Change address from Absolute to Relative reference
GrabAddress = Replace(GrabAddress, "$", "")

'Import data
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "ExcelTable", WbPath, False, GrabAddress

Set xlRng = Nothing
Set xlWs = Nothing
Next
xlWb.Close False
Set xlRng = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

zNumberBox.SetFocus

Like I said, the last line of the code runs but it does not move the focus. I believe the focus is still somewhere on the excel application and not in access but I'm not sure.

Thanks

Hi everyone,
I am having some problems and I am not having any luck solving it. here it is: I am using MS access 2002(XP) and some user are using access 2000.
When I run a process that import a xls into an access table and one field(text) containg "**" two asterisk, it imports just find on my PC but everyone else the field ends been blank. I am the only one that can do the import and have the ** on the table everyone else Nothing.
can't figure out what is the deal
this is the code I am using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "TEST_TRANSFER", "G:TempTransfer" & Me.filename_in & ".xls", 1, "Sheet1!A1:P400"

if anyone have any suggestion please let me know.

thanks

Hello Team,
I am new here. I have one access db with few linked files ( excel and text ). The text file has over 100K rows ( detailed data ) , so I am using interim access table/query to group it and dump into Excel file, which is then again linked to a form in the same DB. ( Reason for this is to easily debug data in excel vs text, so ple. ignore the processing here ).

dumpData.txt
processedExcelFile.xls

I want to give user an ability to do on demand processing, just in case if they have more than one updates in a day. So, I added button on form and in macro I am doing following steps.

1. Close the form ( so linked Excel file is not actively locked )
2. Empty out temporary processing tables in Access.
3. Import text file into that temp table.
4. Drop the linked excel file ( using DoCmd.Deleteobject acTable, "processedExcelFile.xls" )
5. Dump query output (grouped for some criteria ) into Excel file using
DoCmd.TransferSpreadsheet acExport, excel....
6. Relink Excel file into Access as table using DoCmd.TransferSpreadsheet acLink , ...

Problem I run into is step 5, when I am trying to export the query output into excel file, it gives me

"The database engine could not lock table because it is already in use by another person or process. (Error 3211)"

I cannot figure out where is the excel file being locked..
Can anyone please help me ?
Regards,
JB

I am importing an excel worksheet into an existing access 2000 table using TransferSpreadsheet. Im using a timestamp field as well in this table. Once the data is imported I need to find the last record imported and pull the value from the Code field. Then add that value to another table where Code is the Key Value. This is all one continuous operation in the same sub and executing directly after the DoCmd.TransferSpreadsheet

Can anyone point me in the right direction. Ive been struggling for several days and , yes I have searched and searched and experimented and experimented.

Thank you in advance

Hi All

Thoughts on the following welcome, as I've got no clue!

I have code that imports two named ranges from the same spreadsheet to two different tables, snippet as below.


	Code:
	 
    For i = 1 To foundFiles
 
    fileName = .foundFiles(i)
    DoCmd.SetWarnings False
    'Import the summary information for this document to table docDetail
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "docDetail", fileName, True, "docDetail"
    'Import progress of the actions for this document to table approvalProgress
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "approvalProgress", fileName, True, "approvalProgress"
    DoCmd.SetWarnings True
 
    Next i

When I run this with the spreadsheet open both ranges import without errors and all is well. When the spreadsheet is closed (it's intended to pull data from large numbers of these sheets, so this needs to work) the first bit ('docDetail') imports fine, but the second fails with an Error 3270 'Property Not Found' error and no data imported.

Can't even begin to think how to solve this. It's code I've used before (with different ranges) without problem. The only difference is the database is now split with linked tables, but the fact that one works and not the other confuses me...

Cheers for any suggestions!

Rob

Hi everyone.

I'm looking for a better way to auto import data to a table, via a click button on a form. I am currently using the below method...


	Code:
	Private Sub import_Click()
MsgBox "You are about to import the photographers work into this database. Please ensure that the staff database is renamed
'customers.xls' and saved on your desktop.."

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customers", "c:usersphotographerdesktopCustomers.xls", True
DoCmd.Close
DoCmd.OpenForm "Customers"
End Sub

The above has a few drawbacks, mainly that each user / pc has a different logon, so a different path. Each time I update or change the DB, I then have to go round the office and change the code on each PC. The other bad flaw is we cant see which file we are selecting, and I'm relying on people to manually name their xls file correctly.

Does anyone know of some VBA that will go in here to open a browse box, allowing user to browse to ANY .xls file and selecting that?

Have experimented with browsing for files before, never had any success.
Thanks in advance.

I am using DoCmd.TransferSpreadsheet etc. to import data from a MS Excel 97 spreadsheet containing over 50,000 records.

The problem is that it will only import exactly 16,383 records.

If I delete the first 16,383 records in the spreadsheet, it will import the new first set of 16,383 records demonstrating that I don't have a rogue 16,384th record in the xls file.

Any ideas anyone?

Thanks as always.

Hi,

I'm importing Excel-files into Access XP with DoCmd.TransferSpreadsheet.
This works fine.

After the import I want to copy the files into a different directory and rename them. When I try to use FileCopy I get an error 70 saying that the file is used by another process.

There is no other program running that could lock this Excel-file. I only can imagine that Access itself during processing of "TransferSpreadsheet" is locking the file. How can I free the file from this process or what else can I do?

thanks for the help in advance
Gerold

Hi,
I have looked around but still I am unable to find an answer.
How do I use doCmd.TransferSpreadsheet to import an excel sheet to access.

Here is what doesn't work:

	Code:
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DrawingSheet", fileName, True, "DrawingSheet"
'fileName is a variable with the full path and filename of the excel workbook

Quote: Run-time error '3011'; The MS Jet dB engine could not find the object 'DrawingSheet'. Make sure the object exists and that you spell its name and path name correctly. If I make "DrawingSheet" the first sheet and remove it from the statment it works, But I have to import 7 sheets to the database into different tables.

How do it do it??

Hey,

I have this code set up to import all the excel files from a certain
folder, and since these files are protected, i had to place some extra
code in there to open-unprotect-import and close each excel file it
wants to import, which works perfectly fine...

Except that although the excel application is closed it is for some reason still running in the background which causes those particular excel files to open only in read only...unless you ctrl+alt+del and force excel off from the processes... Any ideas on that??

Now The second issue is that i setup that do loop in order to reiterate
throughout the folder and import a specific range from all the xls files there,

But I have a user list table with the fields of 'Name', 'Active' (a checkbox), and 'FileName' pretaining to each user

How would I be able to place conditions within my current code that would only import any user that is active on that table, ofcourse it would have to use the 'FileName' field for each user and concatonate that with the strPath and StrFile

here is the code...


	Code:
	Option Compare Database 
Public xlapp As New Excel.Application 

Public Sub ImportAll() 
    Dim strPath As String 
    Dim strFileName As String 
    Dim strPass As String 
    
    strPath = "G:CBT"                   'Set Path 
    strFileName = Dir(strPath & "*.xls")    'Set first file 

    Do 
        On Error GoTo ErrTrp 
    
        DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" 
        
ErrTrp: 
        If Err.Number = 3161 Then        'Encription error so unprotect workbook 
            xlapp.Visible = False                       'Open Excel 
            xlapp.EnableEvents = False              'Disable Events (Macro's) 
            xlapp.workbooks.Open strPath & strFileName    'Open File 
            xlapp.ActiveWorkbook.Unprotect (strPass)       'Unprotect 
            
            'Try and Import again 
            DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" 

            xlapp.ActiveWorkbook.Protect (strPass)   'protect      
            xlapp.ActiveWorkbook.Save                   'Save 
            xlapp.EnableEvents = True                    'Enable Events 
            xlapp.ActiveWorkbook.Close                  'Close File 
            xlapp.Quit                                           'Quit Excel 
            
        Else 
        End If 
        
        strFileName = Dir()                         'look for next file 
        
        If strFileName = "" Then                    'no more files 
            Exit Do 
        End If 
        
    Loop 

End Sub

Thanks in advance,

Ayyad

When I am importing and Excel Spreadsheet, how do I tell MS Access that I want a specific worksheet from said spreadsheet?

I am using:

DoCmd.TransferSpreadsheet acImport, 8, "Spreadsheet", "J:Reports.xls", True, ""

To import the spreadsheet, but it only allows me to import the first worksheet and I want to import the second.

Thanks for your help...
G

Dear Access Experts

I am trying to import an Excel file using

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportSpecification", "FileName", True

however, when I run the spreadsheet import wizard I don't see an option for saving the specification.

When I run the 'text file' wizard I do have the option of saving the specification. I am using Access 2002 and 2003 and I have not found the option in either for spreadsheets.

How can I define the import specification for excel spreadsheets?

Thanks so much

Peter.

I'm having a strange error when importing a big spreadsheet with Access.

The excel spreadsheet has around 5300 rows and I am using to following command to import:


	Code:
	DoCmd.TransferSpreadsheet acImport, 8, "portal_sgn", "U:old_usersportal_sgn.xls", True, "B2:E"

When I try this it only imports 253 rows. However if the excel spread sheet is open during the import everything gets imported correctly.

I've tried moving the excel file around to a local drive (as oppose to the network drive) and get the same result.

Any suggestions?

Hi,

The spreadsheet file I want to import has 2 sheets in it.

Using this line, I only get the first sheet imported:
DoCmd.TransferSpreadsheet acImport, , "Eroom", path, True

How to import two sheets of into two different tables?

Thanks.


Not finding an answer? Try a Google search.