exporting a table from access to excel

[smiley0q0]

ok, i posted a similar question yesterday (Thread181-76199), i couldn't understand what it was doing, so i built a new spreadsheet from scratch. and it worked fine, but when i came in this morning i checked and it worked fine again, but then i changed my table to new data, same # of rows and columns, just new data, and i am using a macro with "transfer spreadsheet" it goes to the same sheet every time, and i have 2 other sheets in the same workbook, when it exports, it puts all the right data where it is supposed to go,(in the first sheet) but then it pastes over PROTECTED formulae in both my 2 other sheets. and i don't even know where the data is coming from, because it isn't any of the data that was put in the first sheet. i am kind of lost here, anybody else have anything similar happen to them?

Thanks,
Smiley )

 

[Dreamboat]

Are your sheets grouped by chance? or by you? LOL
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[smiley0q0]

no they aren't grouped. they are just the 3 sheets that regularly come up on a new spreadsheet. there are formulae that get data from sheet to sheet, but that shouldn't make ANY difference at all.

 

[Dreamboat]

Something's not right here. Is this a worksheet event or a workbook event? Where's your code located? If it's located on the wrong one, well... I'm sorry. My suggestions sound so lame!
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[smiley0q0]

well i think i found a way around the problem... it only puts stuff on rows a, b and c, so i just moved all my stuff over to row e and over, and made rows a,b,c and d unlocked, but hidden. now it can do whatever it wants to those rows and it won't effect my formulae or my format. so far it is working well.

I have a macro in access that turns warnings off, opens a "make-table-query", transfers the table, and turns warnings back on. when i open the excel spreadsheet it transfered the table to it used to come up with an error saying some data may be lost. but now that those cells are unlocked and no formulae are in them i haven't had the error, but data is still being put in those first rows. i do have a macro in excel in the module, and i also have some code on each sheet. it still baffles me... i don't know what it is doing... hopefully my jerry rigging it will fix it.

thanks
Smiley

 

[Dreamboat]

Hey, guy. Don't hesitate to post the code, ya know.
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[smiley0q0]

this is the excel code, but i don't think it is any part of the problem because the error comes up as soon as i open the file. but here it is anyway...

Sub Update()
' Update Macro
' Macro recorded 4/23/2001 by Jared Taylor
'
Sheets("Tbl30Day&quot.Visible = True
Sheets("Tbl30Day&quot.Select
Range("A1:A60&quot.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"H1:H60&quot, Unique:=True
Range("H2:H14&quot.Select
Selection.Sort Key1:=Range("H2&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Arial"
.Size = 10
.ColorIndex = 1
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Copy
Sheets("30 Day Collection 2&quot.Select
Range("d3&quot.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("d1&quot.Select
Sheets("Tbl30Day&quot.Select
Range("A1&quot.Select
ActiveWindow.SelectedSheets.Visible = False
Range("A1&quot.Select
End Sub

by the way both sheet "30 Day Collection 2" and "30 Day Collection 1" are password protected. on sheet "30 Day Collection 2" d3:d15 is unlocked.

on each individual sheet i have this code...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
EnableSelection = xlUnlockedCells
End Sub

it is a nice piece of code for protection, but only works when the spreadsheet has been open with macros enabled.

if there is any way to get to the vba code of my access macro i don't know about it.
here is what it looks like
setwarnings >no
openquery >Query Name >30DayCompare
>View >datasheet
>Data Mode >Edit
TransferSpreadSheet >Transfer Type >Export
>Spreadsheet type >Microsoft Excel 97
>Table Name >Tbl30Day
>File Name> i:\collect\30 Day Collection.xls
>Has Field Names> No
>Range >(blank)
Setwarnings >Yes


this is all the code i have. if you find anything in here that shouldn't be let me know.

Thanks,
Smiley :-Q

 

[NoBugs]

Could someone give me the syntax for exporting the results from a access2000 query to an excel sheet and drop the header row in the process. I'm have to delete the header row manually.

Thanks in advance,

Troy