VBA code to open workbook, select worksheet, save entire worksheet as CSV file

CLCoop

New Member
Trying to coding VBA in a workbook (Start), vbaproject excel objects: thisworkbook to run the code when it opens.

Want to open workbook that can vary in name (ie: SOF 180530) and then go to worksheet (BAA), select the entire BAA worksheet and save file as a different format CSV (ie: SOFData.CSV)
Once the new file is created to reopen the file and be on the only worksheet SOFData. Want to turn off the pop notifications and override existing files if any.

At this time this is the code I have but get a Run-Time error '9 and for some reason it takes this workbook (start) and makes another file called (SOFData), eek:

'this line works
Code:
Private Sub Workbook_Open()
MsgBox "Hello and Welcome to the FAD Update and Back up Process click OK to PROCEED"

Dim wkb as workbook
Dim sht as worksheet

Set wkb = workbooks.open ("K:\CLC\FAD\*SOF????*.xlsx")   'this line appears to work as the file is in the background 
Set sht = wkb.sheets ("BAA ").Copy                                    'the workbook is in the back ground but not on the right sheet
Thisworkbook =.Saveas filename:="[LEFT][COLOR=#222222][FONT=Verdana]K:\CLC\FAD\SOFData.csv", FileFormate:=x1CSVWindos   
   ActiveWindow.Close

Set wkb= Workbooks.Open("[LEFT][COLOR=#222222][FONT=Verdana]K:\CLC\FAD\SOFData.csv[/FONT][/COLOR]")
[/LEFT]

Dim wb as string
Dim sh as String
Dim Path as String
path = "[LEFT][COLOR=#222222][FONT=Verdana]K:\CLC\FAD\SOFData.csv[/FONT][/COLOR]"
[/LEFT]
wb="SOFData.csv"
sh="SOFData"
Sheets(sh).Active

End Sub
Thanks in advance
[/FONT][/COLOR][/LEFT]
 
Last edited by a moderator:

Norie

Well-known Member
Are you sure this is actually opening workbook?

As far as I'm aware when opening a workbook you need to explicitly state the path and filename.
Code:
Set wkb = workbooks.open ("K:\CLC\FAD\*SOF????*.xlsx")   'this line appears to work as the file is in the background
 

CLCoop

New Member
You can use this kind of code when your not sure of the name or if someone is save a file with a date in it. This line isn't the issue it works great.
For example if you have a file that is updated each day/week/month/year for archiving but you only want to look at the most current this code will see several files like:
SOF 180518
SOF 180630
SOF 180730

Tell your code to find the file that starts with "*SOF1806??*.xlsx" and it will find the file that matches. Nice

Now if I can just figure out the other stuff. So the file opens just fine, but I can't get it to go to the right worksheet. without creating an error.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top