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

[CLCoop]

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

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]

 

[Norie]

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.

Set wkb = workbooks.open ("K:\CLC\FAD\*SOF????*.xlsx")   'this line appears to work as the file is in the background

 

[CLCoop]

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.