|
QuickBooks Keyboard Shortcuts
|
How do I export data from QuickBooks® Basic to Excel?
If you have an old version of QuickBooks® that does not allow you to export reports to Excel,
you can use the following procedure to work around it. You will need to have Microsoft Excel on
your computer. We will breakdown the procedure into two (2) steps:
1. Procedure in QuickBooks® and 2.Procedure in Excel
1. PROCEDURE IN QUICKBOOKS- Create a ‘Tab Delimited File’ in QuickBooks
• Create the report showing the data that you want to export to Excel.
• Print the report to a ‘Tab Delimited File’ by selecting File/Print/Print Report or (CTRL+P).
Change the ‘Print to’ settings to ‘File’ and select ‘Tab Delimited File from the drop down menu.
Click ‘Print’ to send the report to file.
The next step is very important. You will need to tell QuickBooks® two things, where to save the
file “Save in” and the “File Name”. I would suggest that you use ‘My Documents’ as it makes it
easier for you to remember the location of the file when you need it for future use. Please also
choose a simple name for the file as the next time you export to Excel we will be using the same
file and overwriting the data in it.
• Select the ‘Save in’ location and type in the file name and click ‘Save’ when you are done.
2. PROCEDURE IN EXCEL
Here you are going to create a Macro to open the ‘Tab Delimited File’ created by QuickBooks.
This Macro can be used over and over again to convert tab delimited files to Excel files. Also in
the process of creating the Macro, you will open the tab delimited file in Excel after which you
can save the file as an Excel worksheet using whatever name you choose. The steps are as
follows:
• Open a blank Excel worksheet.
• Select Tools/Macro/Macros/Record. The procedure may be slightly different depending
on the version of Microsoft Office that you are using.
• In the Macro dialog box, enter a letter to use with the control key (CTRL) as a shortcut to
open the Macro in the future. Change the ‘store Macro in’ to the ‘Personal Macro Workbook’ and
change the Macro description to something like ‘Convert Tab delimited File’. Click OK and the
Macro will start recording.
• Select file open, make sure that the ‘Look in’ directory is the ‘My Documents’ folder (where
you saved the tab delimited file) and then change the ‘file of type’ to ‘all files’.
• Look for the file that was created in QuickBooks®, click on it once to highlight it and then
click open. This command will open the Excel Text Import Wizard. It will default on the file type
‘Tab Delimited’ so click next. The wizard has three screens, click next until you come to the final
screen then click ‘finished’.
• As soon as the file opens in Excel click the ‘stop recording’ button to stop the Macro.
• Make any formatting changes as necessary and then save the file as an Excel file type
using an appropriate name. When you close the file, you will be prompted to save the Macro in
your personal Macro workbook, click yes and exit.
In the future it will be easy to convert tab delimited files to the Excel format. After you have
overwritten the tab delimited file in the My Documents folder with new QuickBooks® data, simply
open a blank worksheet and run the Macro by holding down the CTRL key plus the letter that
you chose when you created the Macro.
If by chance some reports are not converted ‘perfectly’ when you use the Macro, you can still
use the procedure above to open the tab delimited file but without recording or creating a new
Macro. When the ‘Text Import Wizard’ opens, you can use the three (3) screens to fine tune the
report. Generally the Macro that we created should work OK.
Please note that this procedure is not intended to encourage you not to upgrade to the more
‘feature filled’ versions of QuickBooks® but to the contrary is intended to ‘wet your appetite’ so
that you become interested in the upgraded versions. You can click the QuickBooks® banner at
the top of this page if you need further information or you can follow this link and contact us.
you can use the following procedure to work around it. You will need to have Microsoft Excel on
your computer. We will breakdown the procedure into two (2) steps:
1. Procedure in QuickBooks® and 2.Procedure in Excel
1. PROCEDURE IN QUICKBOOKS- Create a ‘Tab Delimited File’ in QuickBooks
• Create the report showing the data that you want to export to Excel.
• Print the report to a ‘Tab Delimited File’ by selecting File/Print/Print Report or (CTRL+P).
Change the ‘Print to’ settings to ‘File’ and select ‘Tab Delimited File from the drop down menu.
Click ‘Print’ to send the report to file.
The next step is very important. You will need to tell QuickBooks® two things, where to save the
file “Save in” and the “File Name”. I would suggest that you use ‘My Documents’ as it makes it
easier for you to remember the location of the file when you need it for future use. Please also
choose a simple name for the file as the next time you export to Excel we will be using the same
file and overwriting the data in it.
• Select the ‘Save in’ location and type in the file name and click ‘Save’ when you are done.
2. PROCEDURE IN EXCEL
Here you are going to create a Macro to open the ‘Tab Delimited File’ created by QuickBooks.
This Macro can be used over and over again to convert tab delimited files to Excel files. Also in
the process of creating the Macro, you will open the tab delimited file in Excel after which you
can save the file as an Excel worksheet using whatever name you choose. The steps are as
follows:
• Open a blank Excel worksheet.
• Select Tools/Macro/Macros/Record. The procedure may be slightly different depending
on the version of Microsoft Office that you are using.
• In the Macro dialog box, enter a letter to use with the control key (CTRL) as a shortcut to
open the Macro in the future. Change the ‘store Macro in’ to the ‘Personal Macro Workbook’ and
change the Macro description to something like ‘Convert Tab delimited File’. Click OK and the
Macro will start recording.
• Select file open, make sure that the ‘Look in’ directory is the ‘My Documents’ folder (where
you saved the tab delimited file) and then change the ‘file of type’ to ‘all files’.
• Look for the file that was created in QuickBooks®, click on it once to highlight it and then
click open. This command will open the Excel Text Import Wizard. It will default on the file type
‘Tab Delimited’ so click next. The wizard has three screens, click next until you come to the final
screen then click ‘finished’.
• As soon as the file opens in Excel click the ‘stop recording’ button to stop the Macro.
• Make any formatting changes as necessary and then save the file as an Excel file type
using an appropriate name. When you close the file, you will be prompted to save the Macro in
your personal Macro workbook, click yes and exit.
In the future it will be easy to convert tab delimited files to the Excel format. After you have
overwritten the tab delimited file in the My Documents folder with new QuickBooks® data, simply
open a blank worksheet and run the Macro by holding down the CTRL key plus the letter that
you chose when you created the Macro.
If by chance some reports are not converted ‘perfectly’ when you use the Macro, you can still
use the procedure above to open the tab delimited file but without recording or creating a new
Macro. When the ‘Text Import Wizard’ opens, you can use the three (3) screens to fine tune the
report. Generally the Macro that we created should work OK.
Please note that this procedure is not intended to encourage you not to upgrade to the more
‘feature filled’ versions of QuickBooks® but to the contrary is intended to ‘wet your appetite’ so
that you become interested in the upgraded versions. You can click the QuickBooks® banner at
the top of this page if you need further information or you can follow this link and contact us.
Home | Services | About us | Contact us | Opinions | QuickBooks Tips | AVG Anti-virus | Jamaican Payroll
Keyboard Shortcuts
QuickBooks Tips
 |  |  |  |  |
PLEASE NOTE:
There is no claim, suggestion or hint
and comments made on this website
have been endorsed by
QuickBooks® or its makers. All
attempts have been made to ensure
that all articles, contributions etc.
who have years of 'hands on'
experience both as users and
trainers of the various QuickBooks®
accounting solutions.Readers who
intend to make changes to their
software based on suggestions, tips,
ideas etc. on this website are advised
to exercise 'professional skepticism'
before doing so.
There is no claim, suggestion or hint
and comments made on this website
have been endorsed by
QuickBooks® or its makers. All
attempts have been made to ensure
that all articles, contributions etc.
who have years of 'hands on'
experience both as users and
trainers of the various QuickBooks®
accounting solutions.Readers who
intend to make changes to their
software based on suggestions, tips,
ideas etc. on this website are advised
to exercise 'professional skepticism'
before doing so.
