Results 1 to 3 of 3

csv to excel is working but I can't figure out how to save the resulting workbook

Threaded View

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    Reisterstown, MD
    MS-Off Ver
    Excel 2007
    Posts
    2

    csv to excel is working but I can't figure out how to save the resulting workbook

    I have borrowed this code from someone but I can't remember who. It's working great, but I just can't figure out where to name and save the excel workbook. I would like to automate this so it will run and then automatically save the workbook and close the process. This is kind of long, but any help I can get will be terrific. I'm new to the vba in excel coding.

    ThisWorkbook code
    
    Private Sub Workbook_Open()
    'call set reminder
        SetReminder
    
        Dim strDir As String, strFileName As String
        Dim wbSourceBook As Workbook
        Dim wbWriteBook As Workbook
        Dim wsWriteSheet As Worksheet
    
        strDir = "Folder where I have the csv files"      'specify folder to search
        strFileName = Dir(strDir & "*.csv")
    
        Set wbWriteBook = Workbooks.Add
    
        Do While strFileName <> ""
            Set wbSourceBook = Workbooks.Open(strDir & strFileName)
            Set wsWriteSheet = wbWriteBook.Sheets.Add
            wsWriteSheet.Name = strFileName
            wbSourceBook.Sheets(1).UsedRange.Copy wsWriteSheet.Range("A1")
            wbSourceBook.Close False
            strFileName = Dir
        Loop
        
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Stop the procedure automatically on closing the workbook
    
        On Error Resume Next
        
    'Call StopSetReminder to stop Application.OnTime
        StopSetReminder
        
    'Save workbook before closing
    
        ThisWorkbook.Save
           
    End Sub
    
    Module1 Code
    
    Public dTime As Date
    
    Sub SetReminder()
    
        dTime = Now + TimeValue("00:00:05")
    'procedure named SetReminder will autmatically run, at the sheduled time interval, with the OnTime Method.
        Application.ontime dTime, "SetReminder"
        
    'Close the workbook at the specified time:
        If Time = TimeSerial(3, 0, 0) Then
        CloseWorkBook
    
    End If
    
    'If Day(Now) = 1 Then
    'Application.ontime TimeValue("3:00:00"), "Workbook_Open"
    'End If
        
    End Sub
    
    Sub StopSetReminder()
    'Stop the ontime procedure
    
        Application.ontime dTime, "SetReminder", , False
        
    End Sub
    
    Sub CloseWorkBook()
    'Close the process
    
        On Error Resume Next
    
        StopSetReminder
    
        ThisWorkbook.Save
        ThisWorkbook.Close
    
    End Sub
    Last edited by krixham; 01-29-2014 at 01:40 PM. Reason: Request to add code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 04-28-2016, 10:35 AM
  2. [SOLVED] Fixed figure resulting from a time range
    By Khaldon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-08-2012, 06:37 AM
  3. Replies: 3
    Last Post: 08-15-2010, 09:27 PM
  4. Save/Save As not working in Excel 2003
    By CorneliusM in forum Excel General
    Replies: 0
    Last Post: 02-01-2010, 11:12 AM
  5. Shared Workbook; Update-on-Save not working when called from code?
    By Evan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1