[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to format an Excel file

Posted on 2011-05-02
9
Medium Priority
?
182 Views
Last Modified: 2012-05-11
I receive an Excel file weekly that I modify manually to the format I want it. The data I would like to retain on the formatted file is the Patients under the “Patient Name” field who actually made it to their appointment. This is confirmed by seeing the word “Completed” under the “Appt Status” field.  After filtering, the only fields that should remain are “Patient Name”, "Appt Time” & “Appt Status”. The Date format under the "Appt Time" field should change to the format seen in the modified version. Attached are 2 Excel files.  1 is the original data as it is received weekly and the other is the modified version which it should be. The data varies as for the size but the format is the same. Can you please help me create a macro or code I can use to modify this data to save the time I spend doing it manually.  Thanks for your help
Clarity-Original-0311.xlsx
Clarity-0311-Refined.xlsx
0
Comment
Question by:Chrisjack001
  • 5
  • 4
9 Comments
 
LVL 12

Expert Comment

by:danishani
ID: 35506368
See attached zipped database in Access 2007 format.

It imports your original file, modified, and export as refined.

HTH,
Daniel XLS.zip
0
 

Author Comment

by:Chrisjack001
ID: 35507333
How does this work as far as doing this through Access. I have opened your database but what do I do next to accomplish the goal
0
 
LVL 12

Expert Comment

by:danishani
ID: 35507418
I have created a Function ImportXLS to import the XLSX files, modify it by Query, export the Query to Refined.xls and then delete the content of the Import Table.

Click on the Form frmImportExportXLS and then goto the OnClick Event of the Button.
Change the pathnames and filenames into yours.

It will import all importfiles from specified location, so if you dont want that to happen, copy that particular file into a seperate Directory.

If you add True to the Function ImportXLS it will also import all XLSX files in your subdirectories.
 
ImportXLS "C:\YourDirectoryName", True

HTH,
Daniel
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Chrisjack001
ID: 35508652
I have changed the path and file name and when I try to import the file by clicking the Import and Export button in the form I get the error attached to this reply
Error2.docx
0
 
LVL 12

Expert Comment

by:danishani
ID: 35508754
Ok, you might change the name of the Error table into the one you receive.
Or, copy this code instead of the Drop table code;

Dim tbldef As TableDef
For Each tbldef In CurrentDb.TableDefs
If tbldef.Name Like "*_ImportError*" Then
DoCmd.DeleteObject acTable, tbldef.Name
End If
Next tbldef

Open in new window


HTH,
Daniel
0
 
LVL 12

Expert Comment

by:danishani
ID: 35509097
I have created a new database and implemented a user friendly approach.
Furthermore corrected some small errors.
Hope this works fine for you.

Daniel

XLS2.zip
0
 

Author Comment

by:Chrisjack001
ID: 35515542
How does this work as far as doing this through Access. I have opened your database but what do I do next to accomplish the goal. Can you please tell me the steps I should take to accomplish this goal. Thanks for your help
0
 
LVL 12

Accepted Solution

by:
danishani earned 1000 total points
ID: 35515607
So to describe the process in steps;

1. Open the frmImportExportXLS
2. You change the ImportPath location to your location.
2. You change the ExportPath location to your location.
3. You change the FileName to yours.
4. Run the Import and export by clicking on the Button Import and Export XLSX.

The process behind is that it will import all "*.XLSX" files in your selected directory.
I assume that all files are the same format.
Then it will export the qryExportXLS, which is based on the Format you want the Export to look like.

HTH,
Daniel
0
 

Author Closing Comment

by:Chrisjack001
ID: 35770866
I'm sorry for the late response. I had a family emergency and I have been away from my computer for almost 2 weeks. The solution worked great. Thanks very much
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question