Excel and Access

[newbie786]

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]

vFile = "c:\folder\myfile.xlsx"

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

'etc

 

[newbie786]

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

 

[Gasman]

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]

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]

You mean update the Excel sheet? and refresh the form?
I think linked sheets cannot be updated from Access.

 

[newbie786]

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]

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]

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]

How do I refresh the form every 30 seconds?

Would this not be to do with the timeinterval setting?

 

[Gasman]

I believe so, with code in the Timer event, but it is not something I have done yet.
Google is your friend.

 

[Uncle Gizmo]

Google is your friend.

And if you target your Google Search to this Forum only, then you are more likely to get relavent results. I explain how below, and also provide a readymade link for the search page:-


http://www.niftyaccess.com/searching-for-answers/

And this is the "Special Link"

https://www.google.co.uk/advanced_s...en&cr=countryUK|countryGB&safe=off&as_qdr=all


Sent from my SM-G925F using Tapatalk

 

[newbie786]

Managed to sort it with the timing issue
thanks

 

[Gasman]

Managed to sort it with the timing issue
thanks

Please post the resulting code.
You never know, it could help someone else.