Excel and Access (1 Viewer)

newbie786

New member
Local time
Today, 16:09
Joined
Jul 7, 2015
Messages
8
Hi,

I m looking to import data from an excel file but from different worksheets on the same excel file.

I can import the data from a specific worksheet, but I would like to know how to lookup a different worksheet on the same xls document.


thanks
 

Ranman256

Well-known member
Local time
Today, 12:09
Joined
Apr 9, 2015
Messages
4,339
Code:
vFile = "c:\folder\myfile.xlsx"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True,"Sheet1"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True,"Sheet2"

'etc
 

newbie786

New member
Local time
Today, 16:09
Joined
Jul 7, 2015
Messages
8
So I have attached my code with the excel link, and it does show the link with the excel file and im looking to know how I would deal with the update of times when the time lapsed for the month how would I be able to link this to the following spreadsheet on the excel file.

At the moment I only have a link to lookup the single speadsheet.

Have a look and see if you know what I mean.

newbie
 

Attachments

  • displaytimes.zip
    74.5 KB · Views: 100

Gasman

Enthusiastic Amateur
Local time
Today, 16:09
Joined
Sep 21, 2011
Messages
14,044
Not sure what you are trying to do?
If you mean link to another sheet, that is what you would have to do.

I'd be looking at putting a date in column A and the times in B through E for all the days in the year and having them on a named sheet perhaps 2018 and have a new sheet per year, if that is what you are trying to achieve?, otherwise just have them on one sheet.
 

newbie786

New member
Local time
Today, 16:09
Joined
Jul 7, 2015
Messages
8
Hi,

Yeah I was thinking I would have to put them all in one file and have them lookup the date corresponding to todays date.

Im also looking to update the afternoon field and tea field before the time has lapsed, I have used CF on this but not sure why it doesnt automatically update.

any ideas?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:09
Joined
Sep 21, 2011
Messages
14,044
You mean update the Excel sheet? and refresh the form?
I think linked sheets cannot be updated from Access.
 

newbie786

New member
Local time
Today, 16:09
Joined
Jul 7, 2015
Messages
8
I was thinking,

when the time has reached for example [morning] i would like the afternoon to become bold

look at the code in Conditional Formatting
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:09
Joined
Jul 9, 2003
Messages
16,244
This response is in no way relevant to your question! It's just that you named this thread "Excel and Access" so I thought it only right that I should put a link to my blog with a similar title!

"Excel in Access" Think of the SEO!
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:09
Joined
Sep 21, 2011
Messages
14,044
I was thinking,

when the time has reached for example [morning] i would like the afternoon to become bold

look at the code in Conditional Formatting


You conditional formatting works, but you need to refresh the form to get the latest data from the excel sheet.?
 

newbie786

New member
Local time
Today, 16:09
Joined
Jul 7, 2015
Messages
8
How do I refresh the form every 30 seconds?

Would this not be to do with the timeinterval setting?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:09
Joined
Sep 21, 2011
Messages
14,044
I believe so, with code in the Timer event, but it is not something I have done yet.
Google is your friend.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:09
Joined
Jul 9, 2003
Messages
16,244

Users who are viewing this thread

Top Bottom