0
Dim sPath As String

    sPath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments))

    For i = 1 To 50
        Threading.Thread.Sleep(100)
        Application.DoEvents()

    Next

    If My.Computer.FileSystem.FileExists(sPath & "\Housing\Stored Information.xlsx") Then
        Dim APP As New Excel.Application
        workbook = APP.Workbooks.Open(sPath & "\Housing\Stored Information.xlsx")
        worksheet = workbook.Worksheets("Sheet1")
        APP.Visible = False
        MessageBox.Show("File Opened!" & Environment.NewLine & "Path: " & sPath & "\Housing\Stored Information.xlsx")
    Else
        My.Computer.FileSystem.WriteAllBytes(sPath & "\Housing\Stored Information.xlsx", My.Resources.StoredInformation, True)
        Dim APP As New Excel.Application
        workbook = APP.Workbooks.Open(sPath & "\Housing\Stored Information.xlsx")
        worksheet = workbook.Worksheets("Sheet1")
        APP.Visible = False
        MessageBox.Show("File Created!" & Environment.NewLine & "Path: " & sPath & "\Housing\Stored Information.xlsx")
    End If

It's not creating my folder "Housing" or my file "Stored Information.xlsx" Could someone please take a look and tell me what im doing wrong?

  • This question is off-topic here and belongs to SO (stackoverflow.com) – duDE Nov 8 '16 at 9:19
  • i dont see how it can be off topic if it's related to software ? – Anthony Cox Nov 8 '16 at 9:24
  • It's 50/50. It's some VBA you run from within excel, right? So that would make it software related. Otherwise it's a programming question which would be Stackoverflow. Anyway do you get any error messages or is the result just that's not created? What does My.Resources.StoredInformation contain? – Seth Nov 8 '16 at 10:30
  • No error messages appear. It's an excel document that contains headings in four coloumns. My program writes to that excel file and fills each coloumn with values. – Anthony Cox Nov 8 '16 at 10:40
  • It just skips it for some reason, the code is located in the load event – Anthony Cox Nov 8 '16 at 10:41
1

Eventually got it right. The key was to use CreateDirectory() to create the entire directory path including the Housing directory at the bottom of the hierarchy.

Dim sPath As String = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Housing")
Dim Fpath As String = sPath & "\Stored Information.xlsx"

IO.Directory.CreateDirectory(sPath) ' If location already exists it will not do anything

If My.Computer.FileSystem.FileExists(Fpath) = False Then
   My.Computer.FileSystem.WriteAllBytes(Fpath, My.Resources.StoredInformation, True) ' Don't want to append data (although that would not happen in this instance) so True is used for that.
End If

Dim APP As New Excel.Application
workbook = APP.Workbooks.Open(Fpath)
worksheet = workbook.Worksheets("Sheet1")
APP.Visible = False
MessageBox.Show("File Opened!" & Environment.NewLine & "Path: " & Fpath)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.