New Access project

[shaikh]

Hi all.. I am new here and would firstly like to say hi..
The reason i have joined is because i want to write an access database in order to help with some data i have at work..

I dont really use access, however i did a a level project at school based on access. I cant really remember much about it now though.

Basically i have a lot of data on a load of different spreadsheets and worksheets within them.
They are tables of approx 10 x 10 with lets say for e.g a title above the table saing table 001 table 002 etc.

I want to be able to enter table 007 in an access form for example and it to then search through my spreadsheets (spreadsheets wont be open) and then return the 10 x 10 tables which are below the headings table 007 if that makes sense.
Basically i want it to use the spreadsheets as its database information.

Can this be done.. If so how, if not what are my options.
I have about 50 different spreadsheet files each with a lot of worksheets within..

Thanks for any help.

 

[CBrighton]

Is there some reason for keeping the spreadsheets and just having the database as a means to access them rather than importing the data into tables?

 

[shaikh]

No i guess not, but the issue i have is that each of these 10 x 10 tables are on a differnt worksheet, and there are alot! maybe 50 in each file, and there are a few files. Therefore if i had to import this data etc i would take forever!! Is there a better way? would i have to import the data one worksheet at a time..

 

[CBrighton]

If the import procedure is always the same (e.g. always capturing A1 - J10) then you could create a table of the spreadsheet file names and loop though a VBA-based import process.

However it's hard to say without knowing more and to be honest I'd likely do it as a trial and error kind of thing.

But I think the initial investment of time is worthwhile rather than having dozens of external pieces of linked data.


Upgrading from many spreadsheets to a single database seems pointless if all it will be is a glorified file browser to access spreadhseets.

:edit:

And it seems strange to invest time in creating a new UI via Access but then not taking the time to optimise the data.

 

[shaikh]

Upgrading from many spreadsheets to a single database seems pointless if all it will be is a glorified file browser to access spreadhseets.

:edit:

And it seems strange to invest time in creating a new UI via Access but then not taking the time to optimise the data.

Yea i guess you are right..

Is there anywhere i cant find some info on how i can create an import procedure using VBA.
Like i said earlier i have forgotten pretty much everything so need to start from scratch..
Thanks for the help people!

 

[CBrighton]

Given that batch imports aren't always the same you have 2 options, start from scratch or find some existing code and modify it.

Which way you decide to go should be based on how well you read code. If you aren't going to understand existing code which tyou get from another site then you won't know how to modify it and you might be better starting from scratch.

If you think you can manage to work out (or google) pieces of someone elses code and modify it then it will save time. But only if you do understand it.


Personally I'd likely google something like "VBA import multiple spreadsheets" and see what I can find.

It may be worth looking up things like "VBA loops" too so you know how to loop through the same code on multiple spreadsheets.

But realistically we can only point you in the right direction.

 

[shaikh]

Ok so i have sourced some code and had a play around with it..

Function Impo_allExcel()

Dim myfile
Dim mypath
mypath = "J:\PLANNING\Chart\Excel Test Data\"

myfile = Dir(mypath & "*.xls")

Do Until myfile = ""
'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 8, "tblMasterChartData", mypath & myfile
myfile = Dir
Loop

End Function

It works well, however i have a few problems.. Firstly one of the cells in my spreadsheet has text over two lines, this code only imports the first line in the cell.. I guess this is not normally an issue, but i have a titel above each 10 x 10 table that is written in one cell but on two lines.


Secondly, i want to import all worksheets, this code just does the first worksheet. I have been looking around, and think i should be able to work this out. Some information is saying that i need a to first read and store the worksheet name, then import it using a loop to go through every one..