Hi,
I'm going to be giving many people an access database file with a switchboard with quite a few essential queries (query-by-form) so that they can pull data whenever they want. I have a button to export the file into excel as well. The issue I have is with this button.
I used to have this button running a macro that would OutputTo in Excel format. Since this limits me to around 16k records because of it outputting into microsoft excel 5.0, I could not use it. I then changed it to TransferSpreads heet and I didn't put a path where it asked me, i just put the file name. This resulted in the file ending up in my documents (which is fine, because I can't put a full path since each user would not have that path). This is still an issue though since a user could have a large number of files in their my documents and it would be a nuisance to find it each time and if he has to export the same file another day and keep the old file, he would not have a choice to not overwrite the file.
What would be ideal is for that button to export into an excel version that allows the 65k rows and for the user to be asked where they want to put the file. This doesn't have to be the solution, but I can't leave it how it is. Can someone please suggest something?
I am using MS Access 2000 and MS Excel 2000.
Thanks in advance,
Raza
3  3127 
This issue is slowing me down considerably. Right now I'm using TransferSpreads heet, then a msgbox saying "the file is in C:\SomeFile.xls " and then the excel file running using RunApp. This can take up to 20 minutes for some queries. This is a LONG time. I need a remedy.
Help is appreciated.
if anyone can give me ANY insight on this, it would be appreciated...
I can't really help you on the question of speeding up your transfer times; in point of fact I don't even have Excel on my main development machine! Don't ask; it involves a minor flood, my seldom use of spreadsheets and Micro$oft's ridiculous policy of encouraging registration of all software and then not allowing that registration to serve as proof of ownership!
But as to the naming of exported files so as not to overwrite previous files, I can offer a hack I've used in exporting files to other formats. The trick is to export the file to a relative subfolder (relative, that is, to the folder that the database itself is stored in) and to tack on a date or date/time stamp to the name of the exported file. Simply substitute TheFile for the path/filename in whatever method you use to export your file. - TheDate = Month(Date) & "-" & Day(Date) & "-" & Year(Date) & "-at-" & Format(Time, "hh-mm-ampm")
-
-
TheFile = CurrentProject.Path & "\YourSubFolderName\YourFileName-" & TheDate & ".xls"
-
CurrentProject. Path gives you the path to the folder where the database resides. If you wanted to store the exported files in the same folder as the db, instead of in a subfolder, you could use - TheFile = CurrentProject.Path & "\YourFileName-" & TheDate & ".xls"
Likewise, if only a date is desired you could omit the time portion of the name.
Hope this helps a little!
Linq ;0)> Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Randy Harris |
last post by:
I'm using OutputTo to send data into Excel. It has been working nicely.
Now, I need to add more worksheets using OutputTo. Whenever I use it, it
replaces the existing worksheet. Is there any way to get OutputTo to create
more than one worksheet?
|
by: barma16 |
last post by:
I've hit a bit of a brick wall here, and could use some advice. I have
an Access application whose output is a four-tab Excel spreadsheet
where three of the four tabs are the result of database queries.
A number of fields in the queries are designed to become formulae in
Excel after the export has taken place (and they need to be in Excel
rather than Access). I need to use the TransferSpreadsheet Method
rather than the OutputTo to enable...
|
by: paul.hanti |
last post by:
This board has been a plethora of information. Thanks to all that
respond!
I have a quick question regarding the built-in OutputTo macro. I've
built a shared database in Access 2003 (in Access 2000 file format)
that resides on a shared network drive. This OutputTo macro is
essentially setup to export one small table to an Excel spreadsheet
once a user clicks a command button on the switchboard. Another user
and I (both of us have...
|
by: shumaker |
last post by:
I have a form in an Access Project that displays some records, and
based on the displayed records I'd like to output this to an excel
file, but I need to write more fields of the record than are shown in
the form. I'm not sure how I would do this.
I was considering creating a query that has the fields I want to use in
it, but because the form has a "ServerFilter" criteria, I would need a
paramterized query so that the query is filtered...
|
by: deko |
last post by:
When using OutputTo with a query, the 'File name' window in the 'Output To'
dialog gets populated with the name of the query by default. This makes the
exported file self-describing if the query is named on the fly.
The benefit is consistent file naming, and users don't have to enter a file
name when exporting.
The problem is the query has to be renamed every time, and I don't know how
to find it in the QueryDefs collection other than...
| | |
by: Ryan |
last post by:
Hello. I was hoping that someone may be able to assist with an issue
that I am experiencing. I have created an Access DB which imports an
Excel File with a particular layout and field naming. Next the user
can go into a Form which basically a dynamic query with a friendly
interface that eventually outputs a table that is stored in the DB as
well as exported to a CSV file. The CSV file is then used with a
vendor solution to fill in...
|
by: dancole42 |
last post by:
I have a question regarding the DoCmd.OutputTo function.
I have an invoice report that shows all of the invoices for a
particular date. I want to create a button that will export each page
of the report (i.e. each separate invoice) as a separate RTF file, and
save each file with the date, a sequential number (starting with 01
for the first invoice on that date), and the value of the LastName
field for that particular invoice in the...
|
by: Jim Devenish |
last post by:
I have a report which can be displayed on the screen with:
DoCmd.OpenReport "RemittanceAdvice",acViewNormal,,"PaymentID = " &
paymentRecID
I now wish to create a snapshot of this, using the OutputTo command.
Well really I am wishing to use Stephen Lebans procedure to create a
PDF file and an important step in this is the use of the OutPutTo
command:
DoCmd.OutputTo acOutputToReport, RptName, "SnapshotFormat(*.snp)",
|
by: Icarus |
last post by:
I'm using TransferSpreadsheet to import an Excel file in to MS Access
2003.
DoCmd.TransferSpreadsheet acImport, 8, strTable, strFileName, True, ""
One of the columns in the Excel file is a number formatted field, but
occasionally a cell in that column will not have a value.
When that happens, the code is not returning an error, but I end up
with a table of the Import Errors where that blank field value could
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
