Append Information from a Website

[thechazm]

Do not worry about that. If you open the file with excel it lays it out in a standard format. I'll be posting the code today in a module so you can just download it and insert it into your project. I need an example path to your sharepoint file though. Please provide that information and I'll have it out soon.

 

[LadyDi]

Here is a sample link to the SharePoint site.
http://servicehub.com/teams/Service_Reliability/Process/DAAcceptanceData/Forms/AllItems.aspx

 

[thechazm]

That link is to the website housing the list of files as it is an aspx file which is appart of the .net files. We need the direct link to the file itself initially to do the testing. Then after that we can devise a way to read the contents of the aspx from sharepoint to get a list to run through. Baby steps but I need you to try to find the direct link to one of the files.

 

[LadyDi]

Unfortunately, there are not currently any documents on the SharePoint site. They have been talking about the site all week, but they only set it up this morning and the only document on it is an e-mail. Eventually, the documents will all be there, but not yet.

Would we be able to set the database up to point to a folder titled DA_Summary on the hard drive for now, until the SharePoint site got up and running (our IT department is really behind schedule)? Then, when they finally got the SharePoint site running, could we redirect the database? If that will be too complicated, we should probably just direct the database to the hard drive, as I don't know when IT will finish the SharePoint site (it was supposed to be completed and have documents in it yesterday).

 

[thechazm]

No worries that's fine. It's not hard to change things around and you should be able to do that yourself with the code I'll be posting. Anyway I'll be posting the module in about 2-3 hours.

Talk to you then.

 

[LadyDi]

I was just wondering if you would be able to post the module today? My manager is very anxious to see how it will work.

 

[thechazm]

I apologies for dropping the ball. I'll get it to you today.

 

[thechazm]

View attachment code.accdb

I attached a sample database that all it does is open the excel files one by one and printing out some data in the immediate vba window. Try it out and make sure to open the modules so you can change the numbers and letters as you need to for your files.

If you need help let me know.

 

[LadyDi]

Thank you for the module, I really appreciate it. I do have a couple questions. If I understand the VB correctly for the File Operations module, it should allow me to select a .csv file. However, when I tried to see if it would work with the sample file I attached earlier, it would not display that file, for me to select. Why would it do that?

I was also wondering if there would be a way for the module to import all csv files with the word "Summary" in the document name, without needing to do one at a time?

Also, I see where it is pulling information into the "Immediate Window", but how can I get it to import all data and the file name into a table?

 

[thechazm]

it should allow me to select a .csv file. However, when I tried to see if it would work with the sample file I attached earlier, it would not display that file, for me to select.

All you need to do is in the function:
Function GetDataFromExcelSheets(lSheetNumber As Long)
Replace the following line:
BrowseFile "Excel"
With this line:
BrowseFile "CSV"

I was also wondering if there would be a way for the module to import all csv files with the word "Summary" in the document name, without needing to do one at a time?

In the browse window you can select all the ones that have summary in the name by holding down your CTRL button and selecting each one or using your shift button. The browse window is setup to work with multiple files but they need to be in the same directory to select multiple ones.
The function would need to be written differently to support any other meathods.

Also, I see where it is pulling information into the "Immediate Window", but how can I get it to import all data and the file name into a table?

This involves opening a recordset to a table. Which is not hard but I would need an example of a table format to provide any kind of specifics on this.

Hope this helps.

 

[LadyDi]

Thank you for the information. In an earlier post, I included a zip file that contains a database with the table structure I need and a sample csv file.

 

[thechazm]

View attachment code.accdb

Here is an updated version that grabs some of the fields from the CSV example file you provided and puts the data in the table.

If this is what you want just follow the sequence of code that I have in there to finish it out. There was just way to many fields for me to set down and type them all out today.

 

[LadyDi]

That is perfect. Thank you so much. I have requested a few more test files from my co-workers, but then I can show the results to my manager tomorrow. Thank you so much for your assistance.

 

[thechazm]

No worries the best of luck to you.

 

[LadyDi]

I have another question. In talking with my manager this morning, they have finally gotten the SharePoint site up and put some test data on it. The only problem, is that the data is going to be loaded in SharePoint as a zip file, containing multiple zip files. There is going to be a zip file for each day, and then inside that zip file is going to be a zip file for each piece of equipment (that is where the csv file is that I need loaded in the database). Is it possible for the database to unzip the main file and then unzip all files within that and then run the import?

Here is the path to the SharePoint site (\\servicehub.com\teams\Service_Reliability\Process\DAAcceptanceData)

Can the database do all that?

 

[thechazm]

While entirely possible this is going to take a bit more coding as the main name of the zip file has to follow some sort of standard naming scheme other wise you would need to export them yourself anytime you wanted the database to be updated.

 

[LadyDi]

The zip files will all be named "Log Parser" and then the date (i.e. 6-26).

 

[thechazm]

So an example of a file name would be "Log Parser6-26.zip"
Is that correct?

 

[LadyDi]

Yes, that is correct. Only there is a space between Parser and the date. The file name would look like this "Log Parser 6-26.zip".

 

[LadyDi]

I was running some more tests this afternoon and came across one spreadsheet that caused problems for me. Most of these spreadsheets that are getting loaded have a row with column headings and one row of information. Today, I got a report that had multiple rows and for every row, the column headings were copied down. As a result, the database tried to load the column headings as data, which didn't work. Is it possible to remove duplicate values in the spreadsheets before appending them to the database (it needs to be marked for no column headings)? I am attaching a sample to show what I am talking about.