Transfer 
Author Message
 Transfer

Has anyone used the TransferSpreadshet Function?

This is my code

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Test",
"C:\Test.xls", , "a76:z76"

When this runs table "test" is populated with 26 blanks.  Test.xls is the
workbook.  The worksheet I am trying to get the data for is "Summary".  How
do I tell access to use that spreadsheet if this is the problem.

Thanks
Eric



Tue, 25 Dec 2001 03:00:00 GMT  
 Transfer
This is what I use to import spreadsheet "pickup.xls"
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"tblpickups", "pickup.xls", True

This is what I use to Import Spreadsheet "shipment.xls"
 DoCmd.OutputTo acOutputQuery, "QryShpts2", acFormatXLS, "Shipment.Xls",
False

My suggestion is don't{*filter*}with the path name or named range until you get
the importing down.  Both of my xls files are kept in my "my documents"
folder which the function defaults to.

Quote:

>Has anyone used the TransferSpreadshet Function?

>This is my code

> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Test",
>"C:\Test.xls", , "a76:z76"

>When this runs table "test" is populated with 26 blanks.  Test.xls is the
>workbook.  The worksheet I am trying to get the data for is "Summary".  How
>do I tell access to use that spreadsheet if this is the problem.

>Thanks
>Eric



Wed, 26 Dec 2001 03:00:00 GMT  
 Transfer
Let me know the syntax for determining the sheet and if it works.  I could
never get that to work
Quote:

>Has anyone used the TransferSpreadshet Function?

>This is my code

> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Test",
>"C:\Test.xls", , "a76:z76"

>When this runs table "test" is populated with 26 blanks.  Test.xls is the
>workbook.  The worksheet I am trying to get the data for is "Summary".  How
>do I tell access to use that spreadsheet if this is the problem.

>Thanks
>Eric



Wed, 26 Dec 2001 03:00:00 GMT  
 Transfer
Hi Eric,

I had a project about a year ago that I had to transfer a bunch(1,000's) of
spreadhseets...

Using off97...open a module and search help for "transferspreadsheet
method".....I believe your problem is you don't have a fully qualified sheet
name...

Instead of "a76:z76", I think you'll have better luck if you do
"YourSheetName!a76:z76"....

I also discovered I needed to had a "ChDir" statement in the code if I was
bring the data from somewhere other than the default working
directory.......

Below is some code I used...it asks for the directory name and then runs...a
word of caution...Microsoft recs to use DAO instead...The problem I had was
trapping errors for data type mismatches using that method...

With the help of some of the Guru's here, I got it worked out....

Anyway, here's what I used...

**********Start code***********

Dim InputDir As String
Dim InputMsg As String
Dim flist()
Dim answer as string
Dim Eve1 As Integer

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
answer = Dir(InputDir & "\*.xls")

If InputDir = "" Then GoTo Import_all_Exit

ReDim flist(1 To 1)

Do While Len(answer) > 0
    flist(UBound(flist)) = answer
    ReDim Preserve flist(1 To UBound(flist) + 1)
    answer = Dir
Loop

Eve1 = 1

For l = LBound(flist) To UBound(flist) - 1
    answer = flist(l)

Eve1 = Eve1 + 1

ChDir InputDir

DoCmd.TransferSpreadsheet acImport, 8, "Test", answer, 0, "Data!a26:z26"
    'the 0 after answer indocates no header row

If Eve1 Mod 100 = 0 Then    ' If loop has repeated 100 times.
    DoEvents                        ' Yield to operating system.
End If

    'The above was added because I would lose my mouse when doing a lot of
files

Next l

Import_All_Exit:
    Exit sub

**********End Code*************

One other thing:

Access97 reads the first few rows (I think it is 5) to determine the data
type and the assigns the data type to the field...it does this independantly
of what you have the cells formatted in excel...If you start getting import
errors or messages that indicate Access was unable to append records....that
indicates you need to fix the worksheet...I found it much better to repair
these errors in Excel.  I don't know how big a project you are doing or who
is inputing the data into excel.

With the help (and patience) of Dev Ashish and Terry Kreft (and others) in
this ng and Tom Ogilvy (and others) in the Excel ng, I was able to put
together a pretty good system for checking and importing the data.  If you
need help with errors, etc...let me know

HTH,

Bud Dean

Quote:

>Has anyone used the TransferSpreadshet Function?

>This is my code

> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Test",
>"C:\Test.xls", , "a76:z76"

>When this runs table "test" is populated with 26 blanks.  Test.xls is the
>workbook.  The worksheet I am trying to get the data for is "Summary".  How
>do I tell access to use that spreadsheet if this is the problem.

>Thanks
>Eric



Wed, 26 Dec 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Monitoring Data Transfer of Internet Transfer Contol

2. Internet Transfer Control and ASCII transfer Help?

3. Internet Transfer Control and ASCII transfer Help?

4. Internet Transfer Control stalls transfer abruptly

5. Binary FTP Transfer with Inet Transfer Control

6. How to transfer a file greater than 64Kb w/ internet transfer ctrl

7. Setting transfer type in Internet Transfer Control

8. Internet Transfer Control and ASCII transfer Help?

9. ASCII vs Binary Transfer in Iternet Transfer Control

10. HOWTO: Securely transfer a file using the Internet Transfer Control

11. Winsock to transfer information...Do you know another way to transfer information??

12. Autonumber not transferring correctly anymore...

 

 
Powered by phpBB® Forum Software