473,326 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Formatting Output

9
Hi Everyone,

I'm new here and this is my first post. I believe the answer is simple if I knew where to look.

I am exporting a query containing line items and one of the fields in each line is an invoice ID. The problem is the financial app that imports this file is expecting a blank line every time the Invoice ID changes.

When I export the file and manually insert the blank lines in excel the import works fine. I even made a macro in excel that automates this but of course the file gets recreated each export and so overwrites it.

I have tried exporting in CSV, txt and xls it really doesn't matter but the importing app is expecting txt or csv.

My users will be using a Access 2007 runtime and Excel 2003
Oct 15 '07 #1
8 2077
NeoPa
32,556 Expert Mod 16PB
In your position (as there's no easy way to do this in Access) I would create an Excel sheet with the code in which refers to another workbook which is the result of your export. If the export is always produced as the same name and in the same place, you can simply execute your code every time the export is done.
It is possible, though quite complex, to produce the result using Access.
Oct 15 '07 #2
guroos
9
Thanks for the tip, yes it is always the same name and path. I will try this but I was hoping for something more streamlined and transparent for the users.
Oct 15 '07 #3
nico5038
3,080 Expert 2GB
Hmm, when you can use a txt file, then you could use an additional "field" in the query that holds a CRLF (CarriageReturnLineFeed) character to skip a line.
We'll need however a field in the record that's "unique" per invoice ID.
Let's assume you have a unique ItemID, then use:

Expand|Select|Wrap|Line Numbers
  1. select IIF(Dcount("[Invoice ID]","tblInvoice","[Invoice ID]=" & [Invoice ID] & " and ItemID <=" & ItemID) = 1,chr(13) & chr(10),"") as OptionalLine, [Invoice ID], ...
  2.  
The Dcount() won't make it a fast query, but getting the idea ?

Nic;o)
Oct 15 '07 #4
NeoPa
32,556 Expert Mod 16PB
My idea was to UNION the query with a grouped query of essentially the same table. The grouped query would contain a field that had a high value it it such that when sorted it it would always be the last entry for the Invoice.
Each field in the output would use :
Expand|Select|Wrap|Line Numbers
  1. FieldName: IIf([HighVal]<999, [Table].[FieldName], Null)
This should leave an empty line as the last record of each Invoice.
Oct 15 '07 #5
guroos
9
Great ideas guys...I'll give them both a go and report back
Oct 15 '07 #6
NeoPa
32,556 Expert Mod 16PB
Great ideas guys...I'll give them both a go and report back
Let us know how you get on with them :)
Oct 16 '07 #7
guroos
9
OK it's done. I first tried playing with the access code and line returns but due to my lack of skill and time constraints I abandoned it in favour of NeoPa's first suggestion of putting the code in a different excel workbook and it runs like a dream all but transparent to the user.

In case anyone reading this want to know here are the steps:

In Access:
1. Run macro which uses "transferspreadsheet" to create an xls out of my query.
2. Open xls with code: Shell "excel.exe path\file_with_code.xls", vbNormalFocus

In 'file_with_code.xls' call 3 subs on open:
1. First one opens the newly created xls and inserts appropriate blank lines.
Expand|Select|Wrap|Line Numbers
  1. Sub opencsv()
  2.     Workbooks.Open "path\exported_file.xls"
  3.  
  4.    For i = 2 To 1000000
  5.         If Range("H" & i) = "" Then Exit Sub
  6.         If Range("H" & i) <> Range("H" & i + 1) Then
  7.             Rows(i + 1).Insert
  8.             i = i + 1
  9.         End If
  10.     Next i
  11. End Sub
2. Second one saves file as csv suppressing "replace file" dialogue then closes it.
Expand|Select|Wrap|Line Numbers
  1. Application.DisplayAlerts = False
  2.     ActiveWorkbook.SaveAs Filename:= _
  3. "path\exported_file.csv", _
  4. FileFormat:=xlCSV, CreateBackup:=False
  5. Windows("exported_file.csv").Activate
  6. ActiveWorkbook.Close False
3. Third one closes excel:
Expand|Select|Wrap|Line Numbers
  1. Sub close_excel()
  2. Application.Quit
  3. End Sub
Warning:If you are going to use step 3 make copies FIRST with it remmed out in case you need to get back in and change anything, otherwise it will close everytime you open it.

Thanks again guys, couldn't have done it without your suggestions.
Oct 16 '07 #8
NeoPa
32,556 Expert Mod 16PB
Nice one Guroos.
There are other ways to control Excel, and even do it all within the Access code. You probably don't want to worry about that now as it's working, but check out Application Automation in the Articles section. You may find it interesting :)
Oct 16 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Tom Petersen | last post by:
Here is a little more info, sorry should have explained what my final goal was. I am creating a .vcs file from a form to import into Outlook. I was just testing the output on screen then pasting...
6
by: shoo | last post by:
Any one know how to do this? thank Write a simple text-formatting program that produces neatly printed output from input text containing embedded command lines that determine how to format the...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
6
by: shoo | last post by:
Any one know how to do this? thank Write a simple text-formatting program that produces neatly printed output from input text containing embedded command lines that determine how to format the...
7
by: ilona | last post by:
Hi all, I store phone numbers in the database as 123447775665554(input mask is used for input, and some numbers have extensions), and I also know from db if the number is Canadian, US, or some...
2
by: Ken Wilson | last post by:
I am writing and .xml file and am not getting the formatting I would like. The portion of the code that is giving me problems is as follows; XmlTextWriter tw = new XmlTextWriter(filename); ...
8
by: Vinay Jain | last post by:
Hi.. I am newbe in postgresql so please help me though the question may be very easy to answer.. Is there any formatting function to get output with fix lengths..for example my query is.. schema...
9
by: sck10 | last post by:
Hello, I have a page with an ImageButton that is used to redirect to another page. When the page first opens, everything looks as expected. However, when I click on the image, the new page...
6
by: Rafael Olarte | last post by:
The goal of this project is to output the following information as follows: 34.5 38.6 4.1 42.4 3.8 close 46.8 4.4 big change. The values of the first colunm are obtain from a file...
1
by: AJG | last post by:
Hi there. I am using a library called SOCI that has a method to set a stream which it uses to log SQL queries. The signature is as follows: void setLogStream(std::ostream *s); This works great...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.