Exporting and sorting data in Excel from Access
This code works fine for the first worksheet but fails on the subsequent worksheet with "Method Range of object _Global failed" error.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
Basically the central them of my question is this: how can I set output formats for the data when exporting from an Access table to Excel 1997-2003? which - no matter what format it's entered in in Access, or which format the field properties are set to - comes out as dd-m-yy.
In this case, I'd like to have the data in short date format, I.e. dd/mm/y.
Currently I'm exporting the table through a macro - built in the noob macro builder inherent in Access, I.e. no digging in the coding behind the scenes (I'm pretty adept at VBA in Excel, but have yet to tackle this in Access.
I can make it do it behind excel but can't make it a higher priority than excel if that makes sense.
The resulting Excel file will then be imported into SAP as a journal entry.
Because of these requirements, I need help learning how to do more advanced exporting techniques. My boss showed me really quickly one day how I could enter a value on the Excel file which was to contain the export, and Access could read that parameter and return the specific week's data I required.
This is done over a network and some machines don't have Access installed so question is, can I still export excel data into a Access database file even though Access itself may not be installed on that machine. I am not wanting to open Access itself up.
Any ideas how I can "flip a switch" to get this to export properly
I've done some searching around and found similar posts online but I'm having a tough time getting my head round this one.
I have a query called "QryMonthlySales" with about 10 fields and 400 rows of data. I want to export the query to excel, saving the excel file to a folder location chosen by the user via a form.
Before exporting the query to excel, I want to give the user the option to specify the time period. I have a form which allows the user to select a start and end date. Before exporting the query, I will need to select the data via a where clause, something like Period >= txtStartDate and Period <= txtEndDate.
Also, when exporting the data, the column headings are given as the actual field names not as the caption. This looks quite ugly and will be annoying for the user to have to rename
I would like to convert this to a flat file for import and further processing into an Access database. Is it possible to do this without writing custom VBA code? I've tried exporting the data but the sub headers end up as data rows.
I've also tried exporting as XML, DBF etc and re-importing into Access but that doesn't work either.
The reason the Excel file has groupings in the first place is because the third party product we're using only exports in that format.