Using TransferSpreadsheet Macro to import a single sheet from exce

I have an excel workbook with multiple sheets of data.

I am trying to use a TransferSpreadsheet Macro to import 2 individual sheets 
to seperate tables in access. (I have a seperate Macro for each sheet)

What is the correct "format" to identify the individual sheet in the Path?

As an example the file path is:
C:\Documents and Settings\My Documents\Monthly reporting\Monthy Reports for 
Access Import.xls

One of the sheets is labled "Financial Data"  - How do I add this to the end 
of the path so it imports this sheet?

The error I get is "not a valid path" if I try to add the sheet name to end 
of the path


 
Thanks in advance,
Perry K
0
Utf
4/7/2010 8:25:01 PM
access 16762 articles. 3 followers. Follow

1 Replies
1513 Views

Similar Articles

[PageSpeed] 49

On Wed, 7 Apr 2010 13:25:01 -0700, PerryK wrote:

> I have an excel workbook with multiple sheets of data.
> 
> I am trying to use a TransferSpreadsheet Macro to import 2 individual sheets 
> to seperate tables in access. (I have a seperate Macro for each sheet)
> 
> What is the correct "format" to identify the individual sheet in the Path?
> 
> As an example the file path is:
> C:\Documents and Settings\My Documents\Monthly reporting\Monthy Reports for 
> Access Import.xls
> 
> One of the sheets is labled "Financial Data"  - How do I add this to the end 
> of the path so it imports this sheet?
> 
> The error I get is "not a valid path" if I try to add the sheet name to end 
> of the path
> 
>  
> Thanks in advance,
> Perry K

Use the Range argument. 
Did you not read VBA help on the TransferSpreadsheet method?

DoCmd.TransferSpreadsheet  acImport, acSpreadsheetTypeExcel9,
"MyTableName", "c:\FolderName\SpreadsheetName.xls", False, "Financial
Data!A3:A100"

The above will import Column A, rows 3 through 100.
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
4/7/2010 10:15:47 PM
Reply:

Similar Artilces:

error in importing data
Sorry the error is 0x8004032d ...

Message "class not registered" opening sheet with macros Excel 97
I have created a document with Macros and is password protected. I have sent it to several people who are able to use it - the one who can't is using Excel 97 SP2. They are getting error message "class not registered" - could it be the version they are using and how do I make sure they are able to use it. This could simply be a version issue if you developed on a later version and used controls from that versions object libraries. You should always use the lowest version to develop on. If this is not the case look in the VBE on the faulty machine and check tools>ref...

Macro help #12
Hi, I am working on a macro in Excel 2003 Pro, and everytime I go to sort a column of data. There are about 10 rows of data in about 200+ columns. I can only get the macro to sort the column that I edited it with. How can I create the macro, so each time I click on the first row of each column, click the macro button I will assign to the toolbar, then that row will be sorted, and do this for each column? Any help at all would be greatly appreciated, Jeff Garrett (user_jeff@hotmail.com) Hi Jeff please post your current code and then we can suggest changes to it. Cheers JulieD "...

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

exchange 2003
I just set up a mail server at things are working fine in the network where it resides. The issue is that I have 5 other buildings that are connected via a VPN, and they can not connect. Do I need to change settings at their locations? DNS?? WINNS??? It depends on type of client, but generally proper DNS setup is required. Also make sure that no firewalls interfere. laker18 wrote: > I just set up a mail server at things are working fine in > the network where it resides. The issue is that I have 5 > other buildings that are connected via a VPN, and they > can not co...

Using different form strings to filter
Hello. Is there a way to code a query to have it use a filter string if a form is open, or ignore it if the filter is not open? Example: Query Name: Query1 Form: Form1 and Form2 Query fields: Field1 and Field2 I have a string in field 1 and 2 Forms!Form1!Field1 in the first field of the query, and Forms!Form1! Field2 in the second field. Can I also have Forms!Form2!Field1 in the query as well? I have tried this, but a box pops up and asks for the information from form 2 when it is not open. Thank you. hi, nybaseball22@gmail.com wrote: > Hello. Is there a way to code a query to...

Macros #50
How do I move macros from one computer to another? In the VBA editor, right click on the sheet name or module name (in the left panel)that contains the macro and click on "Export File" -- Regards, Dave "Neal" wrote: > How do I move macros from one computer to another? Macros are always stored in workbooks, so you can simply copy the workbook containing the macros from one machine to another. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Neal" <Neal@discussions.microsoft.com> wrote in mess...

Excel 2002
Have several spreadsheet files I use routinely. Three have recently crashed after I added another sheet. In each case the document recovery created a file missing all the color and text formats that the file contained before the crash. Not sure what other changes may have occurred. Is there something wrong with the copy of Excel on my PC? Could these three files be corrupt? Is there a procedure to "clean-up" these files? Thanks in advance for any suggestions. Mark Hi sounds like they are corrupted. I would suggest to copy the data + formats to a new, 'fresh' workbook. ...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

Using Ghost as OS Backup
I attended a class a while back where the exchange admins said they used Ghost to clone the OS drive. I bought the product and it says that during the clone process it temporarily removes the server from the domain. Has anyone done this or had any problems? Thanks, Fred On Mon, 21 Nov 2005 05:26:28 -0600, "Fred T" <fredt2@mail.com> wrote: >I attended a class a while back where the exchange admins said they used >Ghost to clone the OS drive. I bought the product and it says that during >the clone process it temporarily removes the server from the domain. ...

using beforeprint
I am trying to get an application to append a file on a sheet printout. I am using Workbook_BeforePrint. It prints fine, but doesn't seem t execute the sub. What's wrong -- Message posted from http://www.ExcelForum.com Hi you may post your existing code :-) Also check that you have put this code in your workbook module ('ThisWorkbook') -- Regards Frank Kabel Frankfurt, Germany > I am trying to get an application to append a file on a sheet > printout. I am using Workbook_BeforePrint. It prints fine, but > doesn't seem to execute the sub. What's wrong?...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

DPM sometimes uses too many tapes
Hi, i have problem with DPM long term backups on tape. I have collocation enabled so that is not a problem. Sometimes DPM uses too many tapes for same protection group, sometimes it uses 1, and then sometimes 2 - 4. Amount of data is always same. Can anybody help me please, what could be the problem? Hi Tornado, Can you explain protection group configurations as well as tape usage in detail, so that we can analyze on what is happening? Thanks, Jyothsna[MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Tornado" <Torn...

Print Macro #2
Trying to write a macro to print a spreadsheet where the number of rows of data varies. What's wrong with this code? Dim rng As Range Set rng = Range("A1").End(xlDown).End(xlToRight) With ActiveSheet.PageSetup .PrintArea = rng End With Thank you. Pam Hi Pam Leave your PrintArea empty Excel will print all cells with a value on the sheet -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Pam" <gasp0225@juno.com> wrote in message news:16e401c38521$a7d25420$3501280a@phx.gbl... > Trying to write a macro to ...

Importing data into Outlook #2
Is there a process or program with which I can transfer my ACT! data into Outlook contacts? Thanks! ...

Not able to open imported accounts 4.0
We have a situation where we've updated a client from 3.0 to CRM 4.0 and have an import that brings in new Accounts. Unfortunately, we can open accounts that don't have a parent account but can't open the parent account itself. In essence, we have two accounts with another one the parent - we can open ones that show the parent, but again, we get an error - see yor administrator. Is there a rights issue or relational setup we're missing. ...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

Cannot stop using online services with First Tech Credit Union
My bank, First Tech Credit Union, recently upgraded their online banking services. This upgrade also included a change to the user name and password used for online banking in Money 2006. Their upgrade instructions (http://www.firsttechcu.com/help/help_guide_money.html) say to disable online services for your Money file as the third step to upgrading to the new services. The problem is that when I click on Stop Using Online Services in Money nothing happens. I click on OK and Money hangs for a bit then comes back with no change, clicking on OK again has the same effect, clicking on ...

Using paste in macro but with variable references.
Hi all, I am trying to use the paste function in a macro but it needs to be variable. I have split a list of data (team workloads) into the seperate teams and by days of the week. I now need the macro to compile one book with all the data in order. The headings for each team are already set on a worksheet so the data needs to be entered under each heading For example: Team A Day 1 under the heading "Team A Day 1", Team B Day 1 under the next heading on the same sheet "Team B Day 1". I hit problems as the workload ammount will alter from week to week. So of course asking...

Using CComboBox embedded in CToolBar
Hi, I've derived a class from CToolBar and overridden OnCreate to replace 2 placeholder buttons with 2 combo boxes. The two combo boxes are also derived from CComboBox, and populate themselves with appropriate strings. My problem is that I'm not sure of how to inform my application when the user has selected a new item in one of the combo boxes. I've added a handler for the CBN_SELCHANGE message to the CComboBox, but I don't know what to put in it. What is the best way to forward the message to the mainframe? Also, when I replace the placeholder buttons, can I reuse their ...

Sheet display vrs. Print Preview
Why would a sheet display differently than when it is printed. I have an excel file that looks like it prints on my bosses computer but on my pc the page breaks are different the word warps are different. This makes it difficult to work with because it will not print the same on my pc and his. We are both using Excel 2003. Thanks for helping. This could have a great deal to do with the printer drivers. Are you printing to the same printer? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Byron" <Byron@discussions.microsoft.com> wrote in mes...

Excel Macro
I am writing a macro and cannot find any command that will allow me to move the cursor in the Worksheet. Rather than give a Cell address such as "A3" I want to be able to move the cursor any given number of cells to the right,left, up or down. Can anyone help? activecell.offset(x,y).select will move x rows down (if positive, up if negative) and y columns to the right (if positive, left if negative). Stephen King wrote: > > I am writing a macro and cannot find any command that will > allow me to move the cursor in the Worksheet. Rather than > give a Cell address ...

is there a way of using a second monitor as a customer display
This is a multi-part message in MIME format. ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, Does anyone know if rms works with 2 monitors. What I mean can you use a second monitor as customer display or something similar. A lot of pos systems out there use a second monitor as customer display, I m not sure if rms supports that option or not but any help would be appreciated. thanks ------=_NextPart_000_018F_01C86B1D.3EAD2F60 Content-Type: text/html; charset="us-ascii" Content-Transfer-...

Creating Charts for Use in Powerpoint
I have a series of charts that I need to create for a Powerpoin presentation. I thought it would be easy to just create the charts/graphs in Exce and then just cut and paste to the PP-Slides. I'm finding that it isn' quite that easy. Here are some of my problems that I hope someone might be able to she some light on and give me some direction. 1. In Excel how do you control the size of the chart? - When I create a chart 1 and define the data ranges I get a goo looking chart then I thouht, why reinvent the wheel so I just copy th chart to create chart 2 and change the data range, but...

Re:Help with verifying the signature of enveloped -message using x509 certificates.
Re:Help with verifying the signature of enveloped -message using x509 certificates. Hey Friends, I have an xml given by our partner trying to integrating with us and i have their certificate with public key installed in both personal and trusted people folders. when i am using the following code to check the signature of message signed by my certificate i am able to verify the signature but when i am trying to check the signature of the message of our partner using the same following code i am not able to verify the message. the message sent by our partner is right below the code.I wou...