Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the best solution to accomplish this?
Based upon my situation below what is the best way to export records located
in a sheet to an Access format? In my situation, there are multiple users (out in the field & not connected via LAN) that have a copy of the same stand alone spreadsheet file. The excel file has three sheets: one for data entry, another that formats the data into a report and the other which formats the same data into a record format which I want to export to Access. There will be approximately six records created daily by five users (30 new records daily). My vision is that I want this routine to create an export file for each person. Each person will invoke this routine at the end of the day and then they will email the resulting export file to the office. All records will be deleted in the Excel sheet. The office secretary will then run a different routine on the Access side to import each file into Access. Access will then maintain these records. If you have a better solution or any pointers in the right direction will be gratefully received! -Warren |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the best solution to accomplish this?
Have the user export the file as a CSV file, then import it into Access.
Worksheets(3).Copy Activeworkbook.SaveAs Thisworkbook.Path & "\" & "Bob_" _ format(date,"yyyymmdd") & ".csv", xlCSV ActiveWorkbook.Close SaveChanges:=False -- Regards, Tom Ogilvy "WDR" wrote in message ... Based upon my situation below what is the best way to export records located in a sheet to an Access format? In my situation, there are multiple users (out in the field & not connected via LAN) that have a copy of the same stand alone spreadsheet file. The excel file has three sheets: one for data entry, another that formats the data into a report and the other which formats the same data into a record format which I want to export to Access. There will be approximately six records created daily by five users (30 new records daily). My vision is that I want this routine to create an export file for each person. Each person will invoke this routine at the end of the day and then they will email the resulting export file to the office. All records will be deleted in the Excel sheet. The office secretary will then run a different routine on the Access side to import each file into Access. Access will then maintain these records. If you have a better solution or any pointers in the right direction will be gratefully received! -Warren |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the best solution to accomplish this?
Thanks Tom. Does it matter that the data in the sheet is within a certain
range? I don't want to export the whole sheet, just a range. If so, what do I need to do? If it matters, some of the fields are in paragraph form and I am using Excel 2000. "Tom Ogilvy" wrote: Have the user export the file as a CSV file, then import it into Access. Worksheets(3).Copy Activeworkbook.SaveAs Thisworkbook.Path & "\" & "Bob_" _ format(date,"yyyymmdd") & ".csv", xlCSV ActiveWorkbook.Close SaveChanges:=False -- Regards, Tom Ogilvy "WDR" wrote in message ... Based upon my situation below what is the best way to export records located in a sheet to an Access format? In my situation, there are multiple users (out in the field & not connected via LAN) that have a copy of the same stand alone spreadsheet file. The excel file has three sheets: one for data entry, another that formats the data into a report and the other which formats the same data into a record format which I want to export to Access. There will be approximately six records created daily by five users (30 new records daily). My vision is that I want this routine to create an export file for each person. Each person will invoke this routine at the end of the day and then they will email the resulting export file to the office. All records will be deleted in the Excel sheet. The office secretary will then run a different routine on the Access side to import each file into Access. Access will then maintain these records. If you have a better solution or any pointers in the right direction will be gratefully received! -Warren |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the best solution to accomplish this?
The excel file has three sheets: one for data entry, another that formats
the data into a report and the [[other which formats the same data into a record format which I want to export to Access. ]] The obvious answer it to use your macro to build the sheet to support the import into access. If you have fields in paragraph format, then CSV might not be the way to go. Access can also import an excel spreadsheet. -- Regards, Tom Ogilvy "WDR" wrote in message ... Thanks Tom. Does it matter that the data in the sheet is within a certain range? I don't want to export the whole sheet, just a range. If so, what do I need to do? If it matters, some of the fields are in paragraph form and I am using Excel 2000. "Tom Ogilvy" wrote: Have the user export the file as a CSV file, then import it into Access. Worksheets(3).Copy Activeworkbook.SaveAs Thisworkbook.Path & "\" & "Bob_" _ format(date,"yyyymmdd") & ".csv", xlCSV ActiveWorkbook.Close SaveChanges:=False -- Regards, Tom Ogilvy "WDR" wrote in message ... Based upon my situation below what is the best way to export records located in a sheet to an Access format? In my situation, there are multiple users (out in the field & not connected via LAN) that have a copy of the same stand alone spreadsheet file. The excel file has three sheets: one for data entry, another that formats the data into a report and the other which formats the same data into a record format which I want to export to Access. There will be approximately six records created daily by five users (30 new records daily). My vision is that I want this routine to create an export file for each person. Each person will invoke this routine at the end of the day and then they will email the resulting export file to the office. All records will be deleted in the Excel sheet. The office secretary will then run a different routine on the Access side to import each file into Access. Access will then maintain these records. If you have a better solution or any pointers in the right direction will be gratefully received! -Warren |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the best solution to accomplish this?
Thanks again. I'll modify the export sheet to include only the data. If CSV
files are not suggested, what else whould I try? Emailing the excel file is out of the question - too confusing for the users. -Warren "Tom Ogilvy" wrote: The excel file has three sheets: one for data entry, another that formats the data into a report and the [[other which formats the same data into a record format which I want to export to Access. ]] The obvious answer it to use your macro to build the sheet to support the import into access. If you have fields in paragraph format, then CSV might not be the way to go. Access can also import an excel spreadsheet. -- Regards, Tom Ogilvy "WDR" wrote in message ... Thanks Tom. Does it matter that the data in the sheet is within a certain range? I don't want to export the whole sheet, just a range. If so, what do I need to do? If it matters, some of the fields are in paragraph form and I am using Excel 2000. "Tom Ogilvy" wrote: Have the user export the file as a CSV file, then import it into Access. Worksheets(3).Copy Activeworkbook.SaveAs Thisworkbook.Path & "\" & "Bob_" _ format(date,"yyyymmdd") & ".csv", xlCSV ActiveWorkbook.Close SaveChanges:=False -- Regards, Tom Ogilvy "WDR" wrote in message ... Based upon my situation below what is the best way to export records located in a sheet to an Access format? In my situation, there are multiple users (out in the field & not connected via LAN) that have a copy of the same stand alone spreadsheet file. The excel file has three sheets: one for data entry, another that formats the data into a report and the other which formats the same data into a record format which I want to export to Access. There will be approximately six records created daily by five users (30 new records daily). My vision is that I want this routine to create an export file for each person. Each person will invoke this routine at the end of the day and then they will email the resulting export file to the office. All records will be deleted in the Excel sheet. The office secretary will then run a different routine on the Access side to import each file into Access. Access will then maintain these records. If you have a better solution or any pointers in the right direction will be gratefully received! -Warren |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to accomplish this | Excel Discussion (Misc queries) | |||
How do I accomplish this? | Excel Worksheet Functions | |||
Formula or Function to accomplish this? | Excel Worksheet Functions | |||
How can a accomplish these tasks properly??? | Excel Discussion (Misc queries) | |||
how to accomplish a menu_less window? | Excel Programming |