Exporting to excel

[Jpernice]

Hello


Every week I receive new excel file that I upload into access. Then export back into excel into same Workbook and worksheet every week. Is there a export different weeks in the same worksheets?

For example
Weeks
dollars





Thanks

 

[hgus393]

Hi,

I am not sure what you mean. Do you want to export the result from access into the same workbook but to a new tab called for example week1, week2 etc?

Rob

 

[Jpernice]

I would like export the result all the same worksheet.weekly

 

[boblarson]

See if this helps:

http://www.btabdevelopment.com/ts/tq2xlspecwspath

 

[Jpernice]

Hello


Thanks. Two question



First I get a errot that say "Subscript out of range"

Second I how do I set it to add different table to this sheet


Thanks again

 

[boblarson]

You don't modify the function I have. You just call it and pass the parameters.

So, first up - did you modify (at all) the procedure in my link? If so, post the procedure here so we can see what you did (and use code tags here so it formats in an easily read format).

Second, comment out the error handler and run it again and when the dialog comes up, click DEBUG and see which line it highlights.

 

[Jpernice]

Hello

I am very new at this. Thank you for your time

Private Sub Command0_Click()
 
Eval (SendTQ2XLWbSheet)
 
End Sub

 

[boblarson]

1. Get rid of the Eval and parens

2. Second, you pass the query or table name and the worksheet name.

SendTQ2XLWbSheet "MyTableOrQueryNameHere", "WorksheetNameHere", "C:\PathToWorkbookYouWantUpdated.xls"


I think you need to just paste the procedure into the module and do NOT change the function header - Leave this exactly as, is:

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)


And you will need to add some code to the end which will save and close the workbook since this one doesn't have it.

Just before this part:

    rst.Close


    Set rst = Nothing

put this:

ApXL.ActiveWorkbook.Close True

which should save it and close it.

 

[Jpernice]

Hello


I have create a form and then create a button then copy this code into on event section

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
SendTQ2XLWbSheet "2", "Pop", "C:\Documents and Settings\PerniceJ\Desktop\12115\JOn\Jon.xls"
ApXL.ActiveWorkbook.Close True
rst.Close
Set rst = Nothing
 
End Function

When I click the button it doesn't bring back and result

thanks for your time

I am still debug it


Thanks

 

[Jpernice]

Hello


Thanks for your help I got it work.

Just trying to figure out

run-time error 424 object required


Thanks
Jonathan

 

[Jpernice]

Hello


Your funtion work great except it will not close

still getting run-time error 424 object required



Thanks