PDA

View Full Version : "Subscript out of range" failure. Working with two workbooks. ShellExecute



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

snb
11-14-2012, 09:11 AM
Use


workbooks.opentext

Unicco
11-14-2012, 09:20 AM
Use


workbooks.opentext


I've tried that, opentext is just mixing the data aswell as .open does.

shrivallabha
11-14-2012, 10:39 AM
See if the file extension is causing this issue...
http://www.vbaexpress.com/forum/showthread.php?t=32359

Unicco
11-18-2012, 11:21 AM
.OpenXML fixed the problem. Wasn't aware of that, due to the file was .csv