By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Formatting Output

P: 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
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,492
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

P: 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
Expert 2.5K+
P: 3,072
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
Expert Mod 15k+
P: 31,492
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

P: 9
Great ideas guys...I'll give them both a go and report back
Oct 15 '07 #6

NeoPa
Expert Mod 15k+
P: 31,492
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

P: 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
Expert Mod 15k+
P: 31,492
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

Post your reply

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