How do you import a specific tab in an Excel spreadsheet?

[itmasterw]

Hi
I am trying to auto import into Access, through VBA code like this one that I used on another project:

DoCmd.TransferSpreadsheet acImport, 8, "CMS", "C:\products\subproducts\dailyrodusts\prodline.xls", True, ""

I use this to import the Excel spread sheet prodline, and that works great for that project. However, in my current project I need to go to a specific tab or worksheet with in the whole spreadsheet. I am not sure how you would do this?
If any one knows I would really appreciate it.

 

[Elysium]

itmasterw,

After looking in Help for information on the transferspreadsheet command, I found the following quote:

If you are importing from or linking to an Excel version 5.0 or later spreadsheet, you can specify a particular worksheet by using the Range argument.

See if that doesn't help out.

Randy

 

[itmasterw]

I am not sure what htey mean by a range Arguement, and how that relates to the worksheet or tabs.

 

[AccessGuruCarl]

Hi itmasterw,

Here you go. I'm surprised you havn't had an error importing yet. Excel remembers the last worksheet(tab) a user clicked the save command on. The next time excel opens, it opens to that tab! This may not be true for ms access though.
Test it before using the code and let me know.
Open your xls file, click the worksheet(tab) your trying to import but not getting... Click the SAVE button.
Close excel...
Try importing with access, did you get the right sheet this time, or did it import the 1st sheet?

Paste this into a new module, and play around with it!
I left alot of comments, but the code is really short!

Option Compare Database
Option Explicit

Public Sub TranferExcelWorkSheet(strSource As String, _
            strSheet As String, strTable As String)
'Requires a Reference to to Excel
'Requires this ErrorHandler
'Usage: Call TranferExcelWorkSheet("C:\2.xls", "Sheet3", "tblTest")
On Error GoTo ErrorHandler
Dim msExcel As Excel.Application
    Set msExcel = GetObject(Class:="Excel.Application")
    'msExcel.Visible = True 'If you want to see whats going on for testing
    msExcel.Visible = False
    msExcel.Workbooks.Open strSource
    ' What worksheet(tab) do you want active
    msExcel.Worksheets(strSheet).Activate
    ' OR -- not sure which is correct, both work!
    'msExcel.Sheets(strSheet).Activate
    
'You may need this first
'Save the current layout! Excel always remembers the last tab used.
    '    msExcel.ActiveWorkbook.Save
'You may need to close, to import data.
'You need to test this
    '    msExcel.ActiveWorkbook.Close
    
    ' Transfer the worksheet
'    DoCmd.TransferSpreadsheet acImport, 8, strTable, strSource, True, ""
    'Close the workbook
    msExcel.ActiveWorkbook.Close
    ' Close / Exit Excel
    msExcel.Quit
    Set msExcel = Nothing

Exit_TranferExcelWorkSheet:
Exit Sub

ErrorHandler:
    ' Create Excel for the first time if it is not active
    If Err.Number = 429 Then
        Set msExcel = CreateObject("Excel.Application")
        Err.Clear ' Clear Err object in case error occurred.
        Resume
    Else
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
    End If
    Resume Exit_TranferExcelWorkSheet

End Sub

This should get you in the right direction.
Don't forget to post back about saving the worksheet as the current worksheet, then importing! Useful knowledge...

Thanks
Enjoy!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[itmasterw]

HI
THank you I will try it, but the problem is that I reecive a file every day that has aabout thirty tabs and and the tba that I will access will change dayly. So what I am going to do is enter the tab number they need and have it come into the database as a table; and if possable, I really could not have the user open and close the excel file first each time. But again I will look at thsi and see what I can do. Thank you I really appreciate your help.

 

[AccessGuruCarl]

Explain a bit more,
My example... Using "Sheet3"

This is the Tabs name/caption. I used the generic name excel creates when you create a new .xls file.

Will the tab names remain constant?

You can loop the names to get all the worksheets available!
Take a look at the collections object I beleive it shows an example there.

Let me know if I can help out any further.

Good Luck

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[SeaweedOPM]

How can I do this the other way around. I have a button I want to export to .xls only the current record that the form is on. What do I do?

 

[AccessGuruCarl]

Hi zboyles,
Use a Query and the TransferSpreadsheet Method!

Here's how...

Create a query from your table with the fields that are on your form. Makesure youe have a Unique field on the form.

Here is a generic form...
lngProdID(unique), txtProdName, curProdPrice

Now the query... Based on the Product Table
Use the same fields as above.
Paste this into the criteria section for the ProdId
[Forms]![frmProducts]![lngProdID]


Now create a command button on your form,
Paste this code

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Price_Labels", gblPath

Hope this helps...
Let me know if I can be of more assistance


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[SeaweedOPM]

Carl,
This is weird cause I was searching through the forums for something similar to what I had asked before. Anyways, I have alot more since in what i'm doing now. Basically for my current project I would like to take a query (it happends to contain the sales for each day) and upon button click or whatever export the query to an excel file. I can do this no problem, the thing is that i have been creating a new file and nameing it based on the day. What I would really like is a single excel file for each month and, when ran, the daily query would be exported into a tab with the date.

Really the only thing I need is a command to open an excel file, create a tab and export contenents with as little resources as posible.

Thanks

 

[SeaweedOPM]

Update,

Ok so the
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Sales", "C:\test.xls", True

Auto Creates a new tab for each query. I guess if i find a way to rename the query each day before i press the button (maybe rename it back) If you know better way let me know. Also if you know the code to rename a query other than producing a new one each day.

Thx

 

[SeaweedOPM]

Dim mmmmyyyy As String, mmddyy As String, mmm As String, mmmm As String, filename As String


mmmmyyyy = Format$(Date, "mmmm yyyy")
mmddyy = Format$(Date, "mm-dd-yy")
mmm = Format$(Date, "mmm")


On Error Resume Next
MkDir "\\Sales\" & mmmmyyyy

filename = "\\Sales\" & mmmmyyyy & "\" & mmm & " Daily.xls"
DoCmd.Rename mmddyy, acQuery, "Sales"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, mmddyy, filename, True
DoCmd.Rename "Sales", acQuery, mmddyy




This works perfect. I have another query for a month report and i just change the last block acordingly. Hope this helps anyone else.

 

[AccessGuruCarl]

Nice formatting zboyles,

Is your problem solved?

Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[SeaweedOPM]

Yes sir!

Thanks man, i start asking questions then answering them.

 

[AccessGuruCarl]

I know what mean, I do the same. Or find the answer right after I post the question!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[jedel]

Carl,

I've been looking to do a similar thing with an excell spreadsheet, but I want to output the data to excel. I've had a look at your code above and, instead of impoting the transfer spreadsheet, i'm exporting.

I'm getting an error "9 Subscript out of range"

Can you help with this?

Cheers

Dean

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[AccessGuruCarl]

jedel,
Paste some of your code


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[SeaweedOPM]

jedel,

The code that I have up there underneath my Update is for exporting to excel. Basically, sales are entered into the database each day and I want to create an excel sheet with basic sales info in it for others, that are not Access ready, to read. I wanted one xls file to contain a tab for each day with the days sales in it. Then I wanted another xls file containing the whole months sales.

To make this work I created 2 queries, they both had the same info to pull out (only about 4 fields) but they were different in a since that my "salesdate" field = Date() in the daily query; and the Monthly field is "Month([tblOutbound].[SaleDate])" and = Month(Date())

NOTE:
Make sure that "Show" is not checked on the monthly queries "Month([tblOutbound].[SaleDate])" field. It looks wierd but it is made so that it grabs the month value of the sale date and checks that it is the same as todays date (Date()).

Anyways to make this all work you need to run an export for each query. What you might have noticed if you did get the export to work is that it over rights your file each time a query is run. Technically that is not true; it really over writes your sheet. Now i'm sure there are a number of ways to over come this but in the event that I am tracking sales per date i desided to use the current date. It changes every day so that a new sheet is created at the same pace as my queries data changes. (By Date).


SO...

Make a module and a sub that will work as an export method. I declare a few objects to hold values for months, days, and what not. This is the exact code that I run and it works perfect:



Dim mmmmyyyy As String, mmddyy As String, mmm As String, mmmm As String, filename As String


'This sets the format for each date
mmmmyyyy = Format$(Date, "mmmm yyyy")mmddyy = Format$(Date, "mm-dd-yy")
mmm = Format$(Date, "mmm")


'Personally I use a folder for each month
On Error Resume Next
MkDir "\\Sales\" & mmmmyyyy

'Declare filename and i make sure that the file not only changes with what info i want it to hold(month) but i also make sure that it changes with the folder name.
filename = "\\Sales\" & mmmmyyyy & "\" & mmm & " Daily.xls"
DoCmd.Rename mmddyy, acQuery, "Sales" 'This changes the name of my query (Sales) to the current date so that when exported it creates a new sheet for each day.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, mmddyy, filename, True 'Exports to Excel
DoCmd.Rename "Sales", acQuery, mmddyy 'Renames the query back to "Sales" because you need to keep the name consistent with the code.






Let me know if this works. I have the sub run everytime a sale is made. That make sure that it is run on time and updates the export fast.