I DON´T WANT ACCESS TO JUMP EXCEL CELLS !!!

[franksirvent]

Hi there

I have a routine which creates an excel sheet from an Access Database.

The problem is that sometimes some of the columns/fields are empty and when it creates the excel sheet the columns are out of wack!

for example,in access...
field1 field2 field3 field4 field5
1000 10000 1000 1000

field2 is empty so in excel it looks like...
field1 field2 field3 field4 field5
1000 10000 1000 1000

Anyone knows how to get this done correctly ???

thanks

 

[MichaelRed]

With the paucity of information provided, I'm sure there could be a slew of potential ratoinalizations for the behaviour. PERHAPS, if the 'routine' were exposed or explained the plethora of POSSIBLE processes could be narrowed to something approaching reasoonable.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[franksirvent]

Sorry, I didn´t want to get too technical as I thought it would be a "common" problem and someone outhere would understand...

QUOTE CODE
...FIRST I SELECT THE INFORMATION I NEED...
sqlline=" SELECT q.[pol],q.[pod],sqlline = sqlline & " q.[20freight],q.[40freight],q.[40HCfreight], q.[20baf], q.[40baf],q.[shippingline],q.[40THC],q.[40Inland],q.[Charge1],q.[Charge2],q.[Charge3],q.[Charge4],q.[Charge1Desc],q.[Charge2Desc],q.[Charge3Desc],q.[Charge4Desc] INTO ExporttoExcel FROM Quotelines as q WHERE (((q.QuoteID)=" & Val(QuoteID) & &quot) ORDER BY q.POL,q.[20Freight];"

...I RUN THE SQL
DoCmd.runsql sqlline
...I CREATE THE EXCEL FILE FROM THE SQL ABOVE...
DoCmd.TransferSpreadsheet acExport, 8, "ExporttoExcel", excelfile.xls, False, ""

...I then open the Excel sheet created with xlapp.Workbooks.Open (excelfile.xls)
QUOTE ROUTINE

and when the excel opens, the columns are incorrect...if column B5 is empty, then the value which should be on column B6 is put on B5 and so on...
Note that, if the columns are not empty, then it is perfect.

Like I said, this explanation is much more complicated to read...

 

[MichaelRed]

minimmally strange to max weird. with a minimal modification of the snippet posted (just use table / field names from existing table and eliminating the where clause, as well as adding declarations of vars, it works just like 'it should', no 'shuffling of anything where 'gaps' exist in field information. I did also re-write the sql statement a wee bit - but I assume that the changes were simple typos in your post.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JC3]

I don't know what the problem is. However, you can always use DoCmd.TransferText instead. Set up an export spec for tab delimited and name the file with ".xls" extension.

SAMPLE:
DoCmd.TransferText acExportDelim,"SpecName,"QueryName", "FileName.xls", False