saving an Excel Worksheet via Visual Basic 2005 automation

Hot Topic

PLS; help me. I finnaly made my first app... But cant publish it...
coding charts with data in datagrid: is there a good way?
power to USB port
Pointer to an image in memory
How can I get screenshots of every open window?
VB.Net to independent .exe
NullReferenceException
seperating a number
Problem Converting VB6 to .NET
DataTable.Rows.Add()

Visual Basic

Command Button - Delete Key
Dataset InserAt
Multi Text Boxes Validation and error provider
Deployment without .NET
Error when trying to add a reference under "my project"
"*" Question
Newbie class error
Rich text file creation
Test for Web Availability
Obtain day name passing a number

saving an Excel Worksheet via Visual Basic 2005 automation

Hi all, hopefully someone can help with this question, I've searched some forums but not found an instance of it.

I've successfully opened an excel workbook via automation from a VB2005 application. The application populates the cells with data as it is received by the application. I want to use the Excel workbook to store data so that I can in the future access that information (weather data) and display it in Graphs etc or read the data into another application. The problem I'm having is that when I close the application I can't save the workbook, however I can trigger the save dialog box and do it that way but the whole point of the application is to run unattainded and collect and store the information.

Here is the code I've tried,

wkbk.SaveAs("C:\Documents and Settings\Philip Knight\My Documents\Visual Studio 2005\WeatherData" & DateTime.Now)

wkbk being my excel workbook object.

You'll notice I've added the DateTime.Now function in order to save the information in multiple books with out having to change the file name each time.

If i build the project under debug it runs fine until i get to the above line of code where I get an unhandled exception saying the folder does not exsist, although thats right I thought that the SaveAs function would create the neccessary file with my specified path and name. Whats happening

Thanks. Phil

Answer this question

saving an Excel Worksheet via Visual Basic 2005 automation

  • Devin G

    Yes thats right I forgot to format the datetime.now call to remove the reserved file name characters but I've also since found that I need to include the file save format in the call to wkbk.SaveAs

    Like so

    wkbk.SaveAs(FileName, FileFormat:=Excel.XlFileFormat.xlXMLSpreadsheet)

    It now works fine.

    Thanks for all your help.

    Phil

  • r2thej

    You're missing a "\".

    wkbk.SaveAs("C:\Documents and Settings\Philip Knight\My Documents\Visual Studio 2005\WeatherData\" & DateTime.Now)



  • cmaclo

    It's also possible the Now().DateTime isn't returning what you think it is. Does it return this "1/2/2007 13:55"

    You may need to change it to Now().Year & Now().Month & Now().Day & ".txt"

    or whatever you want for that.



  • RustyBadger

    now.tostring("yyyy.MM.dd.mm.ss")

    that will format it nicely for you i would imagine

  • Arun C

    Yeah I see what you're getting at but I did want 'WeatherData' as the file name with the date and time of the save on the end i.e. 'WeatherData01012007915' but prehaps a folder named weatherdata and files in it with just the date and time might be better. My problem still remains even if i just call the file 'weatherdata' i get this exception 'Exception from HRESULT: 0x800A03EC' any further ideas
  • saving an Excel Worksheet via Visual Basic 2005 automation
Answer this question