ST4RCUTTER
Registered User.
- Local time
- Today, 05:47
- Joined
- Aug 31, 2006
- Messages
- 94
This seems fairly simple but I am doing this wrong. I am somewhat experienced in Excel VBA but perhaps because I am making the call from Access I am missing a step here. I have a number of files (usually 5 but the number may vary) that are saved in the .CSV format. I want to open these and then resave them as .XLS files so I can create a linked table in an Access database more easily. I experience problems when linking directly to the .CSV files.
I am trying to open the file, save as XLS, then close the file. This is repeated for as many files as are in the folder. Here is my non-functioning code:
Thanks!
I am trying to open the file, save as XLS, then close the file. This is repeated for as many files as are in the folder. Here is my non-functioning code:
Code:
Sub Resave_source_files()
Call GetData("ECM.csv")
Call GetData("ECM_pending.csv")
Call GetData("MNT.csv")
Call GetData("MNT_pending.csv")
Call GetData("Keystone.csv")
' release static workbook setting in GetData macro
Call GetData("ReleaseWB")
End Sub
Sub GetData(sFname As String)
Static wB As Workbook
Dim wbT As Workbook
Dim sPath As String
If sFname = "ReleaseWB" Then
Set wB = Nothing
Exit Sub
End If
sPath$ = "C:\folder1\folder2\folder3\"
If LCase(Dir(sPath & sFname, vbNormal)) = LCase(sFname) Then
Workbooks.Open Filename:=sPath & sFname
If wB Is Nothing Then
Set wB = ActiveWorkbook
Else
Set wbT = ActiveWorkbook
wbT.Save As Filename:=sPath & sFname & ".xls"
wbT.Close savechanges:=False
End If
End If
End Sub
Thanks!