Unicco
11-14-2012, 08:58 AM
Hello vbaxpress,
I'm getting the subscript out of range error, when trying to execute my 'TransferData'-procedure. When opening the workbook manually, the procedure works prolly, it only occurs when i'm using the shellexecute-command.
The reason why i'm using shellexecute, and not just workbook.open, is course the .csv-format. Workbook.open dosen't arrange the data into coloums.
I've tried rewriting the code several times, but without success. Anyone knows, what's coursing the "subscript out of range"-failure?
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Const SW_SHOWNORMAL = 1
Sub OpenData()
Dim Retval As Long
Dim chDir As String
chDir = """" & ThisWorkbook.Path & Application.PathSeparator & "GeckobookingData.csv" & """"
Retval = ShellExecute(hwnd, "open", "excel.exe", chDir, "c:\", SW_SHOWNORMAL)
Call TransferData
End Sub
Sub TransferData()
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Dim sht As Worksheet
Set wkbDest = ThisWorkbook
Set wkbSource = Workbooks("GeckobookingData")
Set sht = wkbSource.Sheets("GeckobookingData")
sht.Copy wkbDest.Sheets(1)
End Sub
I'm getting the subscript out of range error, when trying to execute my 'TransferData'-procedure. When opening the workbook manually, the procedure works prolly, it only occurs when i'm using the shellexecute-command.
The reason why i'm using shellexecute, and not just workbook.open, is course the .csv-format. Workbook.open dosen't arrange the data into coloums.
I've tried rewriting the code several times, but without success. Anyone knows, what's coursing the "subscript out of range"-failure?
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Const SW_SHOWNORMAL = 1
Sub OpenData()
Dim Retval As Long
Dim chDir As String
chDir = """" & ThisWorkbook.Path & Application.PathSeparator & "GeckobookingData.csv" & """"
Retval = ShellExecute(hwnd, "open", "excel.exe", chDir, "c:\", SW_SHOWNORMAL)
Call TransferData
End Sub
Sub TransferData()
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Dim sht As Worksheet
Set wkbDest = ThisWorkbook
Set wkbSource = Workbooks("GeckobookingData")
Set sht = wkbSource.Sheets("GeckobookingData")
sht.Copy wkbDest.Sheets(1)
End Sub