Send data to existing excel file

[Crowbar]

Hello,
I have been searching here for 2 days and can't find what I need, so I will ask someone to point me in the right direction.

I would like to open a certain excel spreadsheet, then I would like to take the data from the current record in an access form (just 6 fields) and send that data the open spreadsheet. ( 6 cells )

I can modify the spreadsheet if necessary to accomplish this task.
All of the examples I have found create new spreadsheets. I have looked at so many examples my head is spinning.

I like what I see here, ( Always like Bob's stuff! )http://www.btabdevelopment.com/ts/tq2xlspecwspath
but it also creates a new spreadsheet.


Thanks !!!

 

[boblarson]

You can use something like this:

Dim objXL As Object
Dim xlWB As Object
 
Set objXL = CreateObject("Excel.Application")
 
Set xlWB = objXL.Workbooks.Open("yourFileNameAndPath")
 
With xlWB.Worksheets("SheetNameHere")
    .Range("A44").Value = Me.YourTextBoxName
    .Range("B23").Value = Me.YourTextBox2Name
    .Range("G3").Value = Me.YourOtherTextBoxName
End With
 
xlWB.Save
 
xlWB.Close
objXL.Quit
 
Set xlWB = Nothing
Set objXL = Nothing

I think that should be simple enough to get you started.

 

[ghudson]

Are you trying to append the data into an existing spreadsheet (new data added to the next blank row) or are you wanting to override (replace) an existing spreadsheet with the new data?

 

[Crowbar]

You can use something like this:

Dim objXL As Object
Dim xlWB As Object
 
Set objXL = CreateObject("Excel.Application")
 
Set xlWB = objXL.Workbooks.Open("yourFileNameAndPath")
 
With xlWB.Worksheets("SheetNameHere")
    .Range("A44").Value = Me.YourTextBoxName
    .Range("B23").Value = Me.YourTextBox2Name
    .Range("G3").Value = Me.YourOtherTextBoxName
End With
 
xlWB.Save
 
xlWB.Close
objXL.Quit
 
Set xlWB = Nothing
Set objXL = Nothing

I think that should be simple enough to get you started.

Awesome!!! That was simple enough to get me started, and simple enough for me to understand! Thank you Bob! Your posts here to others (and a few to me) have helped me out so many times, I've lost count.

I am already adding lots of other stuff to this.... Wow, I owe you a drink!

 

[Crowbar]

Are you trying to append the data into an existing spreadsheet (new data added to the next blank row) or are you wanting to override (replace) an existing spreadsheet with the new data?

Just trying to fill in a few cells in an existing spreadsheet with VB code. I think I am all set now, but I probably will ask more questions soon. I know just enough to be dangerous....

Thanks for replying!

 

[Crowbar]

One more question on this topic,

I am using data from one of my fields to name the spreadsheet. This field will be numeric, and generate filenames like 4.xls, and 35.xls. So far, so good.

Also, I would like to look in the folder and see the files in numeric order, so I need to add some leading zero's to the field (and the filename) So I enter in the format for the table 0000 and do the same where ever it displays, apparently this does not keep the zero's in the table, but just tacks them on when the number is displayed, because my file name does not contain these zero's.

My question is how the heck do I get these leading zero's into the table? I am trying to mess around with Format in the VB code, but I would really just like to have the value in the table actually be 0004 instead of just 4.

Thanks!

 

[boblarson]

One more question on this topic,

I am using data from one of my fields to name the spreadsheet. This field will be numeric, and generate filenames like 4.xls, and 35.xls. So far, so good.

Also, I would like to look in the folder and see the files in numeric order, so I need to add some leading zero's to the field (and the filename) So I enter in the format for the table 0000 and do the same where ever it displays, apparently this does not keep the zero's in the table, but just tacks them on when the number is displayed, because my file name does not contain these zero's.

My question is how the heck do I get these leading zero's into the table? I am trying to mess around with Format in the VB code, but I would really just like to have the value in the table actually be 0004 instead of just 4.

Thanks!

The only way in the table is to store it as text. It really should just be formatted at the time you need it:

[URL="file://\\MyServerNameHere\ShareName\MyFolder\"]\\MyServerNameHere\ShareName\MyFolder\[/URL] & Format(Me.YourField, "000000") & ".xls"

something like that.

 

[Crowbar]

Thank you again Bob! I thought that's what I would have to do, but I was stuck on making it happen in the table. It's coming along quite well now!