Price book database

[tommyried]

hello everyone,

I want to make price book database in access 2010,the problem is the prices I have are in many excel sheets and PDFS for different vendors and these prices will change after 1 month. I need to track down the price sheets in access database, Is there anyway to do that?

Thanks

[ItsMe]

How many vendors are we talking about? All vendors provide spreadsheets on a monthly basis? You could create a link to each of the vendor's spreadsheets. Each link could be unique to the Vendor ID and you could then query the price cells. Updating the spreadsheet in a predetermined folder with a predetermined name would automatically "update" the prices.

Of course the spreadsheet would always have to be formatted the same way each month.

[Dal Jeanis]

1) The tables in Access won't be directly related to the excel or PDF files - they will have the same information, but in a much better/easier/more useful form

2) Each worksheet that you import, you'll need to import the same way each month, or "attach" the same way each month, to update the price data. You'll need to determine a "key" that identifies those books, for example in the US, the IBN.

3) Since you have both XLS and PDFs involved, you will probably have to create a process to import and update the tables. You will have to determine the method for each spreadsheet or PDF, since the layouts parobably will vary a lot.

[tommyried]

Hi,

I have attached a sample table with two different types of price list one is in excel and the other one is in pdf format now I want to reflect these prices in my acess forms or in database since the pdfs and excel is not properly formatted I cant import these files in my database?

[June7]

Could not use import wizard nor TransferSpreadsheet method. It will require a complicated VBA procedure to import the data.

[tommyried]

hi

Please advice
is there any other way to do it without importing the excel and pdfs in Access?
or any other easy databases which can be implemented easily?

[June7]

I have to say no because the data structure of the spreadsheets is not compatible with database table structure. It will take a lot of VBA code to extract data from the spreadsheets. Forget about extracting from PDFs.

[Dal Jeanis]

Yeah, that's a big programming project. You'd have to figure out Adobe's version of OLE/automation to read the PDF price sheets, and each PDF or Excel spreadsheet layout would require its own import routine. You might be able to track down a PDF-to-Word-Doc converter, but then you'd still have to program the VBA to extract data from each Word doc.

[tommyried]

can we attach pdfs and excel sheets to the database in order to view the price book from the database
I have local network path in which all the price book for different vendors are residing.

[June7]

Files can be saved in an attachment field and then opened for viewing/editing or can be opened from external location by hyperlink.