Importing Excel Spreadsheet

[mattieflo]

Hello,

I'm trying to import two worksheets titled 0-499 and 500-999. However,
apparently my code does not recognize worksheet names with those numbers (or
maybe I'm coding wrong). Is there an issue with importing worksheet with
numbers as its title and if so, is there a way to import these w/o having to
change the worksheet names?

 

[Rick A.B.]

Hello,

I'm trying to import two worksheets titled 0-499 and 500-999. However,
apparently my code does not recognize worksheet names with those numbers (or
maybe I'm coding wrong). Is there an issue with importing worksheet with
numbers as its title and if so, is there a way to import these w/o having to
change the worksheet names?

Difficult to tell if your code is causing the problem with out seeing
your code. If I had to quess I would think that even though the
worksheets titles appear to be numbers they are most likely text not
numbers.

Hope that helps.

Rick

 

[mattieflo]

Hey Rick,

Sorry, here's the code. It probably makes it easier to see.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblAgentTemp", FTP_PATH & File_Name, True, "0-499!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblAgentTemp",
FTP_PATH & File_Name, True, "500-999!"


If I changed the name of the worksheets from 0-499 to ZipZone1 the above
code works perfectly.

 

[Rick A.B.]

Hey Rick,

Sorry, here's the code. It probably makes it easier to see.

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblAgentTemp", FTP_PATH & File_Name, True, "0-499!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblAgentTemp",
FTP_PATH & File_Name, True, "500-999!"

If I changed the name of the worksheets from 0-499 to ZipZone1 the above
code works perfectly.

Mattie,

I don't see anything wrong with your code. What exactly is not
working? Do you get an error? Are you certain the FTP_PATH and
File_Name are correct? You might try and use Debug.Print to print out
the FTP_PATH and File_Name. Are you certain of the Worksheet names?
You might try renaming one of the worksheets, by that I mean rename
worksheet 0-499 to the same name 0-499, the facted that it worked when
you renamed the worksheet ZipZone1 leads me to believe the the
worksheet name is not what it appears. There could be something in
the worksheet name that your not noticing such as a space. Post more
info of you still need help or post if you get it figured out so you
can help someone in the future.

Hope that helps.

Rick

 

[Jeanette Cunningham]

Rick,
your code is trying to import a range from excel.
There are 3 different ways to import a range.

1. If you want to import from A1 to F499, write it like this
--> "A1:F409"

2. If the range is on a particular worksheet in the workbook, write it like
this
Name of sheet ! range of cells
-->"Sheet2!A1:F409"

3. If you have already created a named range in excel, write it like this
"NameOfRange"


Jeanette Cunningham -- Melbourne Victoria Australia

Hey Rick,

Sorry, here's the code. It probably makes it easier to see.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblAgentTemp", FTP_PATH & File_Name, True, "0-499!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblAgentTemp",
FTP_PATH & File_Name, True, "500-999!"

If I changed the name of the worksheets from 0-499 to ZipZone1 the above
code works perfectly.

Mattie,

I don't see anything wrong with your code. What exactly is not
working? Do you get an error? Are you certain the FTP_PATH and
File_Name are correct? You might try and use Debug.Print to print out
the FTP_PATH and File_Name. Are you certain of the Worksheet names?
You might try renaming one of the worksheets, by that I mean rename
worksheet 0-499 to the same name 0-499, the facted that it worked when
you renamed the worksheet ZipZone1 leads me to believe the the
worksheet name is not what it appears. There could be something in
the worksheet name that your not noticing such as a space. Post more
info of you still need help or post if you get it figured out so you
can help someone in the future.

Hope that helps.

Rick